Change Data Structure

[roy00a4]

Dear All

I have a file with 65 different course I'd with status and level as column header and employees details as rows. What I want is to change this horizontal structure to a vertical one. There are a total of 300+ employees. I need the employee details as it is in the rows but repeated for each course id corresponding to it. I have attached a screenshot of what I desire.

I have tried with Pivot and Filter formula and lookups and transpose and everything else that I know.

Please help.

 

[Akuini]

Could you post your data as table using XL2BB? so we don't have to retype it.

 

[roy00a4]

Could you post your data as table using XL2BB? so we don't have to retype it.

	ABC		DEF		GHI
Employee Number	Emp Roles	1111	Level	2222	Level	3333	Level
1234567	Developer		-1	E0	0		-1
2364539	Developer		-1		-1	E0	0
9729836	Developer		-1	E0	0		-1

 

[roy00a4]

	ABC		DEF		GHI
Employee Number	Emp Roles	1111	Level	2222	Level	3333	Level
1234567	Developer		-1	E0	0		-1
2364539	Developer		-1		-1	E0	0
9729836	Developer		-1	E0	0		-1

I am sorry. Cant use XL2BB. I hope this table helps. There are no formulas. Just wana change the structure for a automated dashboard.

 

[Akuini]

Why did you provide different example? in post 1 data has 5 columns, in post 3 it has 8.

 

[roy00a4]

Why did you provide different example? in post 1 data has 5 columns, in post 3 it has 8.

Hi

Does this help?

Name	R52T	Level	T7Y76	Level
A	E0	0		-1
B		-1	E2	2
C	E1	1	E1	1
D	E3	3	E3	3

It is the same example

 

[Anonymous1378]

Assuming your data is in M1:Q5, try

 =HSTACK(DROP(REDUCE("",FILTER(N1:Q1,N1:Q1<>"Level"),LAMBDA(x,y,VSTACK(x,HSTACK(M2:M5,EXPAND(y,4,,y))))),1),WRAPROWS(TOCOL(N2:Q5)&"",2))

 

[roy00a4]

Assuming your data is in M1:Q5, try

 =HSTACK(DROP(REDUCE("",FILTER(N1:Q1,N1:Q1<>"Level"),LAMBDA(x,y,VSTACK(x,HSTACK(M2:M5,EXPAND(y,4,,y))))),1),WRAPROWS(TOCOL(N2:Q5)&"",2))

JUST AMAZING.. THANKS BUDDY..

 

[Peter_SSs]

JUST AMAZING.. THANKS BUDDY..

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.