| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
transpose 1 column of 100 items to 10 x 10 array
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
transpose 1 column of 100 items to 10 x 10 array
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 -- Biff Microsoft Excel MVP "kidsDad" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
transpose 1 column of 100 items to 10 x 10 array
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) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "kidsDad" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I transpose items from another sheet? | Excel Discussion (Misc queries) | |||
| Transpose array sum | Excel Worksheet Functions | |||
| transpose - array problem | Excel Discussion (Misc queries) | |||
| Transpose Array | Excel Discussion (Misc queries) | |||
| Conditional transpose to Array | Excel Discussion (Misc queries) | |||
Linear Mode
