Moving address list to columns from rows

[TWTHOMAS]

Greetings,

I have a lengthy list of addresses that have the individual's names in row one, their street address in row two, and their city-state and zip in row 3.

Because I'm not practiced enough to figure out how to use that list in a mail merge, I'm curious if there is a formula way to move the data from rows 1, 2 and 3 to columns A, B and C.

To be clear, every fourth row is blank currently in my list. Meaning that each address is separated by a blank row.

I tried to use a simple = with a drag down but that results in the day to be all scrambled.

 

[rpaulson]

You want VBA for formula??

 

[TWTHOMAS]

You want VBA for formula??

Prefer formula if possible. I did receive a recommendation to use transpose which works but is a little finicky (meaning I'm not that experienced using it).

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G
1				Names	Street Address	City-State Zip
2	Name1			Name1	Street Address1	City-State Zip1
3	Street Address1			Name2	Street Address2	City-State Zip2
4	City-State Zip1			Name3	Street Address3	City-State Zip3
5				Name4	Street Address4	City-State Zip4
6	Name2			Name5	Street Address5	City-State Zip5
7	Street Address2			0	0	0
8	City-State Zip2			0	0	0
9				0	0	0
10	Name3			0	0	0
11	Street Address3			0	0	0
12	City-State Zip3
13
14	Name4
15	Street Address4
16	City-State Zip4
17
18	Name5
19	Street Address5
20	City-State Zip5
21
22
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=INDEX($A$2:$A$1200,4*(ROW(A1)-1)+1)
E2:E11	E2	=INDEX($A$2:$A$1200,4*(ROW(A1)-1)+2)
F2:F11	F2	=INDEX($A$2:$A$1200,4*(ROW(A1)-1)+3)