Mapping of Data using Row & Column

[ravi2628]

Dear Friends,

Good Evening

i want a formula for the following example.

Macro i cant use because i have restriction so please don't suggest macro.

Input:

User name	Desktop Name	02-Jan-25	03-Jan-25	04-Jan-25	05-Jan-25	06-Jan-25	07-Jan-25	08-Jan-25
100000	LT005	WFO		WFO	WFO	WFO
100000			WO				WO	WO
100008	LT003	WFH	WFH	WFH
100008	LT010				WFO	WFO	WFO	WFO
100011	LT011			WFH
100011	DT021	WFO	WFO			WFO	WFO	WFO
100011					WO
100018	DT021	WFO	WFO	WFO
100018	DT031				WFH	WFH	WFH
100018	LT010							WFO
100020	LT010	WFH	WFH
100020	LT030			WFO		WFO	WFH
100020	DT111				WFO			WFO
100027	LT011	WFO				WFO
100027	DT112		WFO
100027	LT010						WFO	WFO
100027	DT321			WFO
100027					A/L

Output: i was looking.

User Name	02-Jan-25	03-Jan-25	04-Jan-25	05-Jan-25	06-Jan-25	07-Jan-25	08-Jan-25
100000	LT005	LT005	LT005	LT005
100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
100011	DT021	DT021	LT011	DT021	DT021	DT021
100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
100027	LT011	DT112	DT321	LT011	LT010	LT010

Thanks & Regards,
Ravi

 

[lrobbo314]

Here is a way using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NoNull = Table.SelectRows(Source, each ([Desktop Name] <> null)),
    Unpivot = Table.UnpivotOtherColumns(NoNull, {"User name", "Desktop Name"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(Unpivot,{"Value"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[Attribute]), "Attribute", "Desktop Name"),
    OrderColumns = Table.SelectColumns(Pivot,{"User name"} & List.Skip(Table.ColumnNames(Source),2))
in
    OrderColumns

Book9
	K	L	M	N	O	P	Q	R
1	User name	1/2/2025	1/3/2025	1/4/2025	1/5/2025	1/6/2025	1/7/2025	1/8/2025
2	100000	LT005		LT005	LT005	LT005
3	100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
4	100011	DT021	DT021	LT011		DT021	DT021	DT021
5	100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
6	100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
7	100027	LT011	DT112	DT321		LT011	LT010	LT010
Sheet1

My results are a bit different than what you posted. But as far as I can tell, this should be the correct output.

 

[Cubist]

@ravi2628
Can you explain why you're expecting some of the cells to return blank?

 

[ravi2628]

@ravi2628
Can you explain why you're expecting some of the cells to return blank?

the blank represents where our user has not used any system and he is in Week off or absent

 

[Cubist]

the blank represents where our user has not used any system and he is in Week off or absent

It says W/O on Jan 3 but it's not blank in your expected result.

 

[ravi2628]

It says W/O on Jan 3 but it's not blank in your expected result.

It was a Typo Error Mistake

 

[Cubist]

Try:

Book7
	A	B	C	D	E	F	G	H	I
1	User name	Desktop Name	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
2	100000	LT005	WFO		WFO	WFO	WFO
3	100000			WO				WO	WO
4	100008	LT003	WFH	WFH	WFH
5	100008	LT010				WFO	WFO	WFO	WFO
6	100011	LT011			WFH
7	100011	DT021	WFO	WFO			WFO	WFO	WFO
8	100011					WO
9	100018	DT021	WFO	WFO	WFO
10	100018	DT031				WFH	WFH	WFH
11	100018	LT010							WFO
12	100020	LT010	WFH	WFH
13	100020	LT030			WFO		WFO	WFH
14	100020	DT111				WFO			WFO
15	100027	LT011	WFO				WFO
16	100027	DT112		WFO
17	100027	LT010						WFO	WFO
18	100027	DT321			WFO
19	100027					A/L
20
21		1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
22	100000	LT005		LT005	LT005	LT005
23	100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
24	100011	DT021	DT021	LT011		DT021	DT021	DT021
25	100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
26	100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
27	100027	LT011	DT112	DT321		LT011	LT010	LT010
Sheet1

Cell Formulas
Range	Formula
A22:A27	A22	=UNIQUE(A2:A19)
B22:H27	B22	=TOROW(FILTER(IFS($C$2:$I$19<>"",$B$2:$B$19&""),$A$2:$A$19=A22),2,1)
Dynamic array formulas.

If you want a single array then try:

Book7
	A	B	C	D	E	F	G	H
21		1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
22	100000	LT005		LT005	LT005	LT005
23	100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
24	100011	DT021	DT021	LT011		DT021	DT021	DT021
25	100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
26	100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
27	100027	LT011	DT112	DT321		LT011	LT010	LT010
Sheet1

Cell Formulas
Range	Formula
A22:H27	A22	=LET(u,UNIQUE(A2:A19),HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(IFS(C2:I19<>"",B2:B19&""),A2:A19=y),2,1)))),1)))
Dynamic array formulas.

 

[ravi2628]

Try:

Book7
	A	B	C	D	E	F	G	H	I
1	User name	Desktop Name	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
2	100000	LT005	WFO		WFO	WFO	WFO
3	100000			WO				WO	WO
4	100008	LT003	WFH	WFH	WFH
5	100008	LT010				WFO	WFO	WFO	WFO
6	100011	LT011			WFH
7	100011	DT021	WFO	WFO			WFO	WFO	WFO
8	100011					WO
9	100018	DT021	WFO	WFO	WFO
10	100018	DT031				WFH	WFH	WFH
11	100018	LT010							WFO
12	100020	LT010	WFH	WFH
13	100020	LT030			WFO		WFO	WFH
14	100020	DT111				WFO			WFO
15	100027	LT011	WFO				WFO
16	100027	DT112		WFO
17	100027	LT010						WFO	WFO
18	100027	DT321			WFO
19	100027					A/L
20
21		1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
22	100000	LT005		LT005	LT005	LT005
23	100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
24	100011	DT021	DT021	LT011		DT021	DT021	DT021
25	100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
26	100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
27	100027	LT011	DT112	DT321		LT011	LT010	LT010
Sheet1

Cell Formulas
Range	Formula
A22:A27	A22	=UNIQUE(A2:A19)
B22:H27	B22	=TOROW(FILTER(IFS($C$2:$I$19<>"",$B$2:$B$19&""),$A$2:$A$19=A22),2,1)
Dynamic array formulas.

If you want a single array then try:

Book7
	A	B	C	D	E	F	G	H
21		1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25
22	100000	LT005		LT005	LT005	LT005
23	100008	LT003	LT003	LT003	LT010	LT010	LT010	LT010
24	100011	DT021	DT021	LT011		DT021	DT021	DT021
25	100018	DT021	DT021	DT021	DT031	DT031	DT031	LT010
26	100020	LT010	LT010	LT030	DT111	LT030	LT030	DT111
27	100027	LT011	DT112	DT321		LT011	LT010	LT010
Sheet1

Cell Formulas
Range	Formula
A22:H27	A22	=LET(u,UNIQUE(A2:A19),HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(IFS(C2:I19<>"",B2:B19&""),A2:A19=y),2,1)))),1)))
Dynamic array formulas.

Thanks Cubist thanks for logic