transpose 1 column of 100 items to 10 x 10 array

  • Thread starter Thread starter kidsDad
  • Start date Start date
K

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
 
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
 
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)
Click to expand...

Typo in the formula range. Should be:

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