Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to single column
I'm sure this has been discuss before, but It was not quite what I
want to do, I want to go from A D G J M B E H K N C F I L O to A B C D E F G H I J K L M N O newbie excel user, TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to single column
Dan,
Use this formula in a cell =INDEX($A$1:$E$5,MOD(ROW()-ROW($A$7)-1,ROWS($A$1:$E$5))+1,INT((ROW()-ROW($A$7)-1)/COLUMNS($A$1:$E$5))+1) Watch the line wrapping.... Replace the three instances of $A$1:$E$5 with the address of the block of data, replace the two instances of $A$7 with the address of the cell one row above where you place this formula (both should be in absolute style - with the $ $ ) and then copy down until you get an error. HTH, Bernie MS Excel MVP wrote in message ... I'm sure this has been discuss before, but It was not quite what I want to do, I want to go from A D G J M B E H K N C F I L O to A B C D E F G H I J K L M N O newbie excel user, TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to single column
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to single column
If you can use a macro, this will **move** the contents of whatever is range
is selected from a rectangular array of values to a linear column of values (preserving the first column of the selection)... Sub ListSelection() Dim X As Long Dim NumRows As Long Dim NumCols As Long NumRows = Selection.Rows.Count NumCols = Selection.Columns.Count For X = 1 To NumCols - 1 Selection(1).Offset(NumRows * X, 0).Resize(NumRows, 1).Value= _ Selection(X + 1).Resize(NumRows, 1).Value Next Selection(1).Offset(0, 1).Resize(NumRows, NumCols - 1).Clear End Sub If there are formulas involved in your selection array, then change the two occurrences of "Value" to "Formula". Rick wrote in message ... I'm sure this has been discuss before, but It was not quite what I want to do, I want to go from A D G J M B E H K N C F I L O to A B C D E F G H I J K L M N O newbie excel user, TIA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to single column
Another one:
Assume your data is in the range E1:I3 Assume you want the data extracted starting in cell A1. Enter this formula in A1 and copy down until you get a contiguous return of 0s: =OFFSET(E$1,MOD(ROWS($A$1:A1)-1,3),INT((ROWS($A$1:A1)-1)/3)) Where 3 = number of rows in your table. Or, assuming there are no empty cells within the table, this version will return blanks once you've extracted all the data: =IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),OFFSET(E$1,MOD(R OWS($A$1:A1)-1,3),INT((ROWS($A$1:A1)-1)/3)),"") Or, this version calculates the number of rows in the table: =IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),OFFSET(E$1,MOD(R OWS($A$1:A1)-1,ROWS(E$1:I$3)),INT((ROWS($A$1:A1)-1)/ROWS(E$1:I$3))),"") Or, if you have the Morefunc.xll add-in from http://xcell05.free.fr/english/ then this array formula** entered in A1 and copied down: =INDEX(ARRAY.JOIN(TRANSPOSE(E$1:I$3)),ROWS(A$1:A1) ) With an error trap: =IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),INDEX(ARRAY.JOIN (TRANSPOSE(E$1:I$3)),ROWS(A$1:A1)),"") -- Biff Microsoft Excel MVP wrote in message ... I'm sure this has been discuss before, but It was not quite what I want to do, I want to go from A D G J M B E H K N C F I L O to A B C D E F G H I J K L M N O newbie excel user, TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From single cell variables to a single column serie | New Users to Excel | |||
Converting an array of data into a single column | Excel Discussion (Misc queries) | |||
Creating a single vertical array from multiple column arrays | Excel Worksheet Functions | |||
Filling excel entire row/column instead of single cell from an array | Excel Worksheet Functions | |||
Coverting array data into single column | Excel Worksheet Functions |