transpose 1 column of 100 items to 10 x 10 array

[kidsDad]

I frequently copy data into EXCEL that comes in as a column of data, but
needs to be put in an array to mail merge ect.
ie. name, address, city, zip repeated for a hundred names in column A,
needs to go into an array 4 columns wide and 20 rows deep.
thanks in advance, rob

 

[T. Valko]

One way...

Assume you have data like this in the range A2:A9

name1
address1
city1
zip1
name2
address2
city2
zip2

Enter this formula in C2:

=INDEX($A$2:$A$21,(ROWS(C$2:C2)-1)*4+COLUMNS($A1:B1)-1)

Copy across to F2 then down to C3:F3

The result will be:

name1...address1...city1...zip1
name2...address2...city2...zip2

 

[T. Valko]

Assume you have data like this in the range A2:A9

=INDEX($A$2:$A$21,(ROWS(C$2:C2)-1)*4+COLUMNS($A1:B1)-1)

Typo in the formula range. Should be:

=INDEX($A$2:$A$9,(ROWS(C$2:C2)-1)*4+COLUMNS($A1:B1)-1)