Formula to transform report from the column model into the list

[eddorena]

Hi All,

I tried to transform the column report into the list and I tried to mix several formulas such Xlookup + Hlookup + Index, Match to avoid transform manually since the actual data is so much line.

Whether possible to transform what I am expected by using mix formula?

I attached the sample file (I need to transform from the Time Sheet Summary into target summary)

Transform Summary Report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Time Sheet summary										Target Summary
3	Working Code	Month	Working Area	Jhon	Mike	Daren	Jonas				Employee ID	Employee Name	Month	Working Code	Working Area	Working Hrd
4	DA-210123	Sep'22	Warehouse	5	5		2					Jhon	Sep'22	DA-210123	Warehouse	5
5	DA-210874	Sep'22	Office	2		2						Jhon	Sep'22	DA-210874	Office	2
6	DA-210899	Sep'22	Pool		1	3	4					Jhon	Sep'22	DA-210221	Resto	4
7	DA-210221	Sep'22	Resto	4	5	4						Jhon	Sep'22	DA-210009	Kids Zone	3
8	DA-210009	Sep'22	Kids Zone	3		4						Mike	Sep'22	DA-210123	Warehouse	5
9												Mike	Sep'22	DA-210899	Pool	1
10												Mike	Sep'22	DA-210221	Resto	5
11												Daren	Sep'22	DA-210874	Office	2
12												Daren	Sep'22	DA-210899	Pool	3
13												Daren	Sep'22	DA-210221	Resto	4
14												Daren	Sep'22	DA-210009	Kids Zone	4
15												Jonas	Sep'22	DA-210123	Warehouse	2
16												Jonas	Sep'22	DA-210899	Pool	4
17
Sampel

Thank You and have a nice Day

 

[Fluff]

The header range needs to be Hdr,'SSID PLAN Oct'!M1:Y1 to match the last column in the various monthly sheets.

 

[eddorena]

The header range needs to be Hdr,'SSID PLAN Oct'!M1:Y1 to match the last column in the various monthly sheets.

Hi @Fluff

Finally, I can modify the formula, I skipped to understand LstA name and change the MOD value from +1 to +2, after reading carefully and found this issue, now I can add new columns and also add new Sheet.

FYI, I am put credit with your name and this forum in my update history since someone, perhaps needs to understand this complex formula (I hope you don't mind)

 

[Fluff]

Glad you sorted it & thanks for the feedback.