Use row values in column1 as table column headers.

[csenor]

Hello everyone. I have a table of addresses that I need to transform. Here's a sample table. I need to pivot this data so there's 3 columns labeled Name, Street, and City. Then the column 2 data would transpose every 3 lines of data. Can anyone help?

Column1	Column2
Name	DELSEA SHOPRITE
Street	1255 W Landis Ave
City	Vineland, NJ 08360
Name	RITE AID # 10487
Street	7 W Landis Ave
City	Vineland, NJ 08360
Name	CVS STORE #00695
Street	76 Landis Ave
City	Vineland, NJ 08360
Name	CVS STORE #05855
Street	1163 E Chestnut Ave
City	Vineland, NJ 08360
Name	INSPIRA MEDICAL CENTER
Street	1505 W Sherman Ave
City	Vineland, NJ 08360
Name	RITE AID # 7822
Street	2791 S Delsea Dr
City	Vineland, NJ 08360

 

[Toadstool]

Hi CSenor,

Does this do what you want?

Csenor.xlsx
	A	B	C	D	E	F
1	Column1	Column2		Name	Street	City
2	Name	DELSEA SHOPRITE		DELSEA SHOPRITE	1255 W Landis Ave	Vineland, NJ 08360
3	Street	1255 W Landis Ave		RITE AID # 10487	7 W Landis Ave	Vineland, NJ 08360
4	City	Vineland, NJ 08360		CVS STORE #00695	76 Landis Ave	Vineland, NJ 08360
5	Name	RITE AID # 10487		CVS STORE #05855	1163 E Chestnut Ave	Vineland, NJ 08360
6	Street	7 W Landis Ave		INSPIRA MEDICAL CENTER	1505 W Sherman Ave	Vineland, NJ 08360
7	City	Vineland, NJ 08360		RITE AID # 7822	2791 S Delsea Dr	Vineland, NJ 08360
8	Name	CVS STORE #00695
9	Street	76 Landis Ave
10	City	Vineland, NJ 08360
11	Name	CVS STORE #05855
12	Street	1163 E Chestnut Ave
13	City	Vineland, NJ 08360
14	Name	INSPIRA MEDICAL CENTER
15	Street	1505 W Sherman Ave
16	City	Vineland, NJ 08360
17	Name	RITE AID # 7822
18	Street	2791 S Delsea Dr
19	City	Vineland, NJ 08360
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=INDEX($B:$B,(ROW()*3)-4)&""
E2:E19	E2	=INDEX($B:$B,(ROW()*3)-3)&""
F2:F19	F2	=INDEX($B:$B,(ROW()*3)-2)&""

 

[Crystalyzer]

Beat me to the punch, I used TRANSPOSE

Book1
	A	B	C	D	E	F
1	Column1	Column2		Name	Street	City
2	Name	DELSEA SHOPRITE		DELSEA SHOPRITE	1255 W Landis Ave	Vineland, NJ 08360
3	Street	1255 W Landis Ave		RITE AID # 10487	7 W Landis Ave	Vineland, NJ 08360
4	City	Vineland, NJ 08360		CVS STORE #00695	76 Landis Ave	Vineland, NJ 08360
5	Name	RITE AID # 10487		CVS STORE #05855	1163 E Chestnut Ave	Vineland, NJ 08360
6	Street	7 W Landis Ave		INSPIRA MEDICAL CENTER	1505 W Sherman Ave	Vineland, NJ 08360
7	City	Vineland, NJ 08360		RITE AID # 7822	2791 S Delsea Dr	Vineland, NJ 08360
8	Name	CVS STORE #00695
9	Street	76 Landis Ave
10	City	Vineland, NJ 08360
11	Name	CVS STORE #05855
12	Street	1163 E Chestnut Ave
13	City	Vineland, NJ 08360
14	Name	INSPIRA MEDICAL CENTER
15	Street	1505 W Sherman Ave
16	City	Vineland, NJ 08360
17	Name	RITE AID # 7822
18	Street	2791 S Delsea Dr
19	City	Vineland, NJ 08360
Sheet5

Cell Formulas
Range	Formula
D2:F7	D2	=TRANSPOSE(INDIRECT("B"&ROW()*3-4&":B"&ROW()*3-2))
Dynamic array formulas.

 

[csenor]

Thank you! That worked.