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

 

[eddorena]

I do not download anything from that site as it is dodgy. Can you upload it to somewhere like OneDrive, DropBox, or GoogleDrive mark for sharing & post the link you are given here.
Also please do not password protect the file, all members need to be able to access it.

HI @Fluff

IC, and duly noted.

This is the One Drive link: TS_Summary_AUG.xlsx

 

[Fluff]

Thanks for that, with the exception of M2 none of those cells are actually empty.
Try this instead

=LET(Ra,M1:X1,Rb,M2:X100,r,ROWS(Rb),s,SEQUENCE(COLUMNS(Rb)*r,,0),a,INDEX(Ra,INT(s/r)+1),b,INDEX(A2:L100,MOD(s,r)+1,{2,1,3}),c,INDEX(Rb,MOD(s,r)+1,INT(s/r)+1),sc,SEQUENCE(,5),FILTER(IF(sc=1,a,IF(sc<5,INDEX(b,s+1,sc-1),c)),c<>""))

 

[eddorena]

Thanks for that, with the exception of M2 none of those cells are actually empty.
Try this instead

=LET(Ra,M1:X1,Rb,M2:X100,r,ROWS(Rb),s,SEQUENCE(COLUMNS(Rb)*r,,0),a,INDEX(Ra,INT(s/r)+1),b,INDEX(A2:L100,MOD(s,r)+1,{2,1,3}),c,INDEX(Rb,MOD(s,r)+1,INT(s/r)+1),sc,SEQUENCE(,5),FILTER(IF(sc=1,a,IF(sc<5,INDEX(b,s+1,sc-1),c)),c<>""))

Hi @Fluff

I appreciate it , its work and I am noted the difference , ( c<>"" )

Thank You Very Much @Fluff

 

[Fluff]

Glad to help & thanks for the feedback.

 

[eddorena]

Glad to help & thanks for the feedback.

Hi @Fluff

Sorry, just light Question. Did LET function possible to use as mix/combine formula?

Example : next month my data use different sheet for Sep, however, still use same sheet for summary from Aug and will continue until end of year, my imagination such this ( =LET(xAUG datax) + LET(xSep datax) + etc )

Thank You

 

[Fluff]

Not sure what you are asking for.

 

[eddorena]

Not sure what you are asking for.

Hi @Fluff

I mean like this attached sample

TS_Summary_FY22.xlsx
	B	C	D	E	F	G
2	Jhon	Sep'22	DA-140200-DD01	Warehouse	5	SEP
3	Jhon	Sep'22	DA-140201-DD19	Office	2
4	Jhon	Sep'22	DA-140201-DD21	Resto	4
5	Jhon	Sep'22	DA-140201-DD22	Kids Zone	3
6	Mike	Sep'22	DA-140200-DD01	Warehouse	5
7	Mike	Sep'22	DA-140201-DD20	Pool	1
8	Mike	Sep'22	DA-140201-DD21	Resto	5
9	Daren	Sep'22	DA-140201-DD19	Office	2
10	Daren	Sep'22	DA-140201-DD20	Pool	3
11	Daren	Sep'22	DA-140201-DD21	Resto	4
12	Daren	Sep'22	DA-140201-DD22	Kids Zone	4
13	Jonas	Sep'22	DA-140200-DD01	Warehouse	2
14	Jonas	Sep'22	DA-140201-DD20	Pool	4
15	Jhon	Sep'22	DA-140200-DD01	Warehouse	10	AUG
16	Jhon	Sep'22	DA-140201-DD19	Office	10
17	Jhon	Sep'22	DA-140201-DD21	Resto	5
18	Jhon	Sep'22	DA-140201-DD22	Kids Zone	5
19	Mike	Sep'22	DA-140200-DD01	Warehouse	2
20	Mike	Sep'22	DA-140201-DD19	Office	2
21	Daren	Sep'22	DA-140200-DD01	Warehouse	33
22	Daren	Sep'22	DA-140201-DD19	Office	33
23	Daren	Sep'22	DA-140201-DD20	Pool	33
24	Jonas	Sep'22	DA-140201-DD20	Pool	6
25	Jonas	Sep'22	DA-140201-DD21	Resto	6
26	Jonas	Sep'22	DA-140201-DD22	Kids Zone	6
Summary

Cell Formulas
Range	Formula
B2:F14	B2	=LET(Nama,Aug!D3:G3,MH,Aug!D4:G8,r,ROWS(MH),s,SEQUENCE(COLUMNS(MH)*r,,0),a,INDEX(Nama,INT(s/r)+1),b,INDEX(Aug!A4:C8,MOD(s,r)+1,{2,1,3}),c,INDEX(MH,MOD(s,r)+1,INT(s/r)+1),sc,SEQUENCE(,5),FILTER(IF(sc=1,a,IF(sc<5,INDEX(b,s+1,sc-1),c)),c<>""))
B15:F26	B15	=LET(Nama,Sep!D3:G3,MH,Sep!D4:G8,r,ROWS(MH),s,SEQUENCE(COLUMNS(MH)*r,,0),a,INDEX(Nama,INT(s/r)+1),b,INDEX(Sep!A4:C8,MOD(s,r)+1,{2,1,3}),c,INDEX(MH,MOD(s,r)+1,INT(s/r)+1),sc,SEQUENCE(,5),FILTER(IF(sc=1,a,IF(sc<5,INDEX(b,s+1,sc-1),c)),c<>""))
Dynamic array formulas.

Whether possible to mix both formulas (or more than 2) so every month someone fill the other month the sheet summary will automated showing the result.

I appreciate your guidance

 

[Fluff]

Whilst it is possible to do that, to do it for an entire year you would be better off using a macro or power query.
If you want to go down that route, then you would need to start a new thread.

 

[eddorena]

Whilst it is possible to do that, to do it for an entire year you would be better off using a macro or power query.
If you want to go down that route, then you would need to start a new thread.

Hi @Fluff

to be honest, every new fiscal year I'll reset the summary and archive previous fiscal year, so the data will not continue for every year

I was thinking to use macro before, however, using O365 and .xlsm still have minor issues by using MS Teams and I still am waiting investigation from my IT division related security

Btw, glad to know if possible and if little bit tricky, it's Ok, I can understand.

Anyway, I want to say Thank You so much, this is the second time you help me with LET and I fully understand how LET formulas working.

 

[Fluff]

You're welcome & thanks for the feedback.