Data Transpose

[ritrio314]

Hello All,

I need help in transposing the data below using Table ID as uniqueID, basically every time the integer in TableID increments, create a new row with the data transposed. Thank you in advance.

TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender
1​	5​	What's your favorite color?	Green				1​	5​	Green	Yes	Male
1​	5​	Do you reside in California?	Yes				2​	7​	Yellow	No	Female
1​	5​	Client's Gender	Male				3​	9​	Purple	No	Unknown
2​	7​	What's your favorite color?	Yellow
2​	7​	Do you reside in California?	No
2​	7​	Client's Gender	Female
3​	9​	What's your favorite color?	Purple
3​	9​	Do you reside in California?	No
3​	9​	Client's Gender	Unkown

 

[ExceLoki]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender
2	1	5	What's your favorite color?	Green				1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes				2	7	Yellow	No	Female
4	1	5	Client's Gender	Male				3	9	Purple	No	Unkown
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Client's Gender	Female
8	3	9	What's your favorite color?	Purple
9	3	9	Do you reside in California?	No
10	3	9	Client's Gender	Unkown
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=UNIQUE(FILTER(A2:A100,""<>A2:A100))
I2:I9	I2	=IFNA(VLOOKUP(H2,$A$1:$B$99,2,FALSE),"")
J2:J9	J2	=IFERROR(FILTER($D$2:$D$100,($J$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
K2:K9	K2	=IFERROR(FILTER($D$2:$D$100,($K$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
L2:L9	L2	=IFERROR(FILTER($D$2:$D$100,($L$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender
2	1	5	What's your favorite color?	Green				1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes				2	7	Yellow	No	Female
4	1	5	Client's Gender	Male				3	9	Purple	No	Unkown
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Client's Gender	Female
8	3	9	What's your favorite color?	Purple
9	3	9	Do you reside in California?	No
10	3	9	Client's Gender	Unkown
11
12
13
Master

Cell Formulas
Range	Formula
H2:L4	H2	=HSTACK(UNIQUE(FILTER(A2:B100,A2:A100<>"")),WRAPROWS(TOCOL(D2:D100,1),3))
Dynamic array formulas.

 

[ExceLoki]

that works as long as the data is sorted properly (as it is in the example from the OP), but if it's not then will have jumbled results as shown below:

multiple posts solutions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender		TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender
2	1	5	What's your favorite color?	Green				1	5	Green	Yes	Male		1	5	Green	Yes	Female
3	1	5	Do you reside in California?	Yes				2	7	Yellow	No	Female		2	7	No	Yellow	Male
4	2	7	Client's Gender	Female				3	9	Purple	No	Unkown		3	9	Purple	No	Unkown
5	3	9	Do you reside in California?	No
6	2	7	What's your favorite color?	Yellow
7	1	5	Client's Gender	Male
8	3	9	What's your favorite color?	Purple
9	2	7	Do you reside in California?	No
10	3	9	Client's Gender	Unkown
Data Transpose

Cell Formulas
Range	Formula
H2:H4	H2	=UNIQUE(FILTER(A2:A100,""<>A2:A100))
I2:I7,I9:I10	I2	=IFNA(VLOOKUP(H2,$A$1:$B$99,2,FALSE),"")
J2:J7,J9:J10	J2	=IFERROR(FILTER($D$2:$D$100,($J$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
K2:K7,K9:K10	K2	=IFERROR(FILTER($D$2:$D$100,($K$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
L2:L7,L9:L10	L2	=IFERROR(FILTER($D$2:$D$100,($L$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
N2:R4	N2	=HSTACK(UNIQUE(FILTER(A2:B100,A2:A100<>"")),WRAPROWS(TOCOL(D2:D100,1),3))
Dynamic array formulas.

 

[ritrio314]

Thank you so much Fluff and ExceLoki,

Both solutions worked until some point because I found out that not every client has the same number of questions. Therefore, this creates a bigger problem because I cannot use a standard header for everyone. The only way could be creating separate batches for each client with their set of questions as their header. Is this possible? Thank you so much.

TableID

VendorID

Question

Response

TableID

VendorID

What's your favorite color?

Do you reside in California?

Client's Gender

1​

5​

What's your favorite color?

Green

1​

5​

Green

Yes

Male

1​

5​

Do you reside in California?

Yes

1​

5​

Client's Gender

Male

2​

7​

What's your favorite color?

Yellow

---------->

TableID

VendorID

Whats your favorite color?

Do you reside in California?

Whats your marital status

Clients Gender

2​

7​

Do you reside in California?

No

​

​

2

7

Yellow

No

Marrie

Female

2​

7​

Whats your marital status

Married

2​

7​

Client's Gender

Female

3​

9​

What's your favorite color?

Purple

TableID

VendorID

What's your favorite color?

Do you reside in California?

3​

9​

Do you reside in California?

No

3​

9​

Purple

No

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	TableID	VendorID	Question	Response		TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender	Whats your marital status
2	1	5	What's your favorite color?	Green		1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes		2	7	Yellow	No	Female	Married
4	1	5	Client's Gender	Male		3	9	Purple			Single
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Whats your marital status	Married
8	2	7	Client's Gender	Female
9	3	9	What's your favorite color?	Purple
10	3	9	Whats your marital status	Single
11
Main

Cell Formulas
Range	Formula
F1:K1	F1	=HSTACK(A1:B1,TOROW(UNIQUE(FILTER(C2:C100,C2:C100<>""))))
F2:G4	F2	=UNIQUE(FILTER(A2:B100,A2:A100<>""))
H2:K4	H2	=IFNA(INDEX(D2:D100,XMATCH(TAKE(F2#,,1)&DROP(F1#,,2),A2:A100&C2:C100,0)),"")
Dynamic array formulas.

 

[ExceLoki]

as long as you're good with all the returns being in the same table, ^ or this should work for you.
if you want/need them broken up into separate tables based on table id, then we'd need to rework these solutions.

multiple posts solutions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender	Whats your marital status
2	1	5	What's your favorite color?	Green				1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes				2	7	Yellow	No	Female	Married
4	1	5	Client's Gender	Male				3	9	Purple			Single
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Whats your marital status	Married
8	2	7	Client's Gender	Female
9	3	9	What's your favorite color?	Purple
10	3	9	Whats your marital status	Single
Data Transpose

Cell Formulas
Range	Formula
J1:M1	J1	=TOROW(UNIQUE(FILTER(C2:C100,""<>C2:C100)))
H2:H4	H2	=UNIQUE(FILTER(A2:A100,""<>A2:A100))
I2:I10	I2	=IFNA(VLOOKUP(H2,$A$1:$B$99,2,FALSE),"")
J2:M10	J2	=IFERROR(FILTER($D$2:$D$100,(J$1=$C$2:$C$100)*($O2=$A$2:$A$100)),"")
Dynamic array formulas.

 

[ritrio314]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	TableID	VendorID	Question	Response		TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender	Whats your marital status
2	1	5	What's your favorite color?	Green		1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes		2	7	Yellow	No	Female	Married
4	1	5	Client's Gender	Male		3	9	Purple			Single
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Whats your marital status	Married
8	2	7	Client's Gender	Female
9	3	9	What's your favorite color?	Purple
10	3	9	Whats your marital status	Single
11
Main

Cell Formulas
Range	Formula
F1:K1	F1	=HSTACK(A1:B1,TOROW(UNIQUE(FILTER(C2:C100,C2:C100<>""))))
F2:G4	F2	=UNIQUE(FILTER(A2:B100,A2:A100<>""))
H2:K4	H2	=IFNA(INDEX(D2:D100,XMATCH(TAKE(F2#,,1)&DROP(F1#,,2),A2:A100&C2:C100,0)),"")
Dynamic array formulas.

This works! Thank you so much!!!!!!!

 

[ritrio314]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	TableID	VendorID	Question	Response	---->			TableID	VendorID	What's your favorite color?	Do you reside in California?	Client's Gender
2	1	5	What's your favorite color?	Green				1	5	Green	Yes	Male
3	1	5	Do you reside in California?	Yes				2	7	Yellow	No	Female
4	1	5	Client's Gender	Male				3	9	Purple	No	Unkown
5	2	7	What's your favorite color?	Yellow
6	2	7	Do you reside in California?	No
7	2	7	Client's Gender	Female
8	3	9	What's your favorite color?	Purple
9	3	9	Do you reside in California?	No
10	3	9	Client's Gender	Unkown
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=UNIQUE(FILTER(A2:A100,""<>A2:A100))
I2:I9	I2	=IFNA(VLOOKUP(H2,$A$1:$B$99,2,FALSE),"")
J2:J9	J2	=IFERROR(FILTER($D$2:$D$100,($J$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
K2:K9	K2	=IFERROR(FILTER($D$2:$D$100,($K$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
L2:L9	L2	=IFERROR(FILTER($D$2:$D$100,($L$1=$C$2:$C$100)*($H2=$A$2:$A$100)),"")
Dynamic array formulas.

Thank you so much ExceLoki!!!

 

[Fluff]

Gad we could help & thanks for the feedback.