Today we are going to discuss about different methods in excel to create series of numbers like 1,1,1,2,2,2,3,3,3,4... and 1,2,3,1,2,3,1,2,3,1... Lot to learn, let's dive in.
To extract data from multiple column table into one column table or List, we need to create this kind of numbers in order to form row and column numbers of the table.
B3:D12 is our source data,
So, we are going to create series of numbers like H4:H30 for rows and I4:I30 for columns.
We will use this attributes to feed INDEX function to get our final output.
F3:F30 is our final output.
Method - 1:
Series for row numbers,
=INT((ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))-1)/COLUMNS($B$4:$D$12))+1
Let me start from scratch,
=COUNTA(-$B$4:$D$12)
This function helps us to get the count of non blank cells in the range B4:D12.
In our case, blank cells need to be included in count and so we add minus sign before the range reference. It delivers 27.
Now, we can create sequence of numbers from 1 to 27 using Row/Indirect construction,
=ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))
Output of above construction is,
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27}
Divide this sequence with number of columns in range and get the integer part,
=INT(ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))/COLUMNS($B$4:$D$12))
={0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9}
We are almost there, but needs to offset by one and so, we deduct the sequence by one and then add one after getting integer numbers.
={1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9}
Series for Column numbers,
=MOD(ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))-1,COLUMNS($B$4:$D$12))+1
This is same as row numbers, but here, we get reminder after division using MOD function
Output looks like this,
={1;2;3;1;2;3;1;2;3;1;2;3;1;2;3;1;2;3;1;2;3;1;2;3;1;2;3}
Now we are going to feed these sequences in to INDEX function as row number and column number.
The formula in F4 cell,
=INDEX($B$4:$D$12,INT((ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))-1)/COLUMNS($B$4:$D$12))+1,MOD(ROW(INDIRECT("1:"&COUNTA(-$B$4:$D$12)))-1,COLUMNS($B$4:$D$12))+1)
Method - 2:
Series for row numbers,
=INT((SEQUENCE(COUNTA(-B4:D12))-1)/COLUMNS(B4:D12))+1
Series for Column numbers,
=MOD((SEQUENCE(COUNTA(-B4:D12))-1),COLUMNS(B4:D12))+1
Here, SEQUENCE function helps us to replace the ROW/INDIRECT construction. But you need to have Excel 2019 or later version to access this function.
Method - 3:
This is not related to sequence of numbers, so consider this as bonus method,
In office 365 version, we can use TOCOL function to convert multiple column table into one column.
=TOCOL($B$4:$D$12)
Arguments of this function,
=TOCOL(array,[ignore],[scan_by_column])
Ignore argument helps us to ignore blank cells, Errors and the default is to keep all values.
scan by column argument helps us to scan the data by column before appending and the default is to scan by row.
Comments