Tom’s Tutorials For Excel: Transform a Range Into a Single Column List
Here’s how you can take a range of data comprised by multiple columns and rows, and list its items in a single column.
In the picture, all the items in range A1:D5 are listed in column F with the formula
=INDEX($A$1:$D$5,ROWS($1:4)/4,MOD(ROWS($1:1)-1,4)+1)
that is entered into cell F1 and copied down as far as you’d need to in order to capture all the cells in the data range.
Note in the formula that the number 4 represents the fourth column in the range, which in this example is column D.
Thanks
Thanks
how I can Transform a Range Into a Single row
Formula, VBA, or manually are the most common ways, depending on how many rows and columns, and what order the data appears on the single row, meaning is it transposed by order of rows or by columns or randomly.
how can convert the up example as : 463 ,163 , 722 , 921 , 873 ,512 , 20 ,6 , 131 ,333 ….
See my reply to your duplicated reply.
how convert the up example as : 463 , 163 , 722 , 921 , 873 , 512 , 20 , 6 , 131 , 333 ……
Now honestly, look at what you first asked and what you replied here with. How am I or anyone supposed to reply with an answer you can use. Explain yourself and your worksheet.
You first said
“how I can Transform a Range Into a Single row”
I replied with
“depending on how many rows and columns, and what order the data appears on the single row, meaning is it transposed by order of rows or by columns or randomly.”
You replied with a string of numbers but did not address what I said were the dependent factors.
So, just say what you have, and where you have it, and what you want. Nobody reading this except you has ever seen your worksheet.
How many columns is your data range?
What columns does your data range occupy?
What rows does your data range occupy?
So, what is the range of your data range?
Then what I asked which you ignored, is
“is it transposed by order of rows or by columns or randomly.”
Just say what you want the data range to end up looking like, if by order of rows or columns.
Example: Your data range is 3 columns wide and 3 rows deep, in range A1:C3.
– A B C
1 Y D J
2 F B L
3 X G R
Now, just say what you want: single row by rows or by columns of that data. And WHICH row do you want it on? Staring from what cell?
Do you want
Y D J F B L X G R
or do you want
Y F X D B G J L R
or do you want it random.
Don’t forget to say what the address is of the original data range. And how it looks. All of this is what you need to explain, otherwise I have no idea what you want or what you are working with.
how can convert the up example as : 463 ,163 , 722 , 921 , 873 ,512 , 20 ,6 , 131 ,333 ….
See my reply on your duplicated reply.
Thanks, it words perfect
Thank you!
I was also thinking on the same Mohamed’s problem. But unfortunately Mohamed did not cleared your valid question on three options. TOM URTIS, if e consider your first option: Y D J F B L X G R. What are the changes to be done in your vba codes to get the range in one row? Thanks.
Meanwhile, I have slightly modified your above vba code and now it is working perfectly for ‘single row by rows’: Y D J F B L X G R
=INDEX($D$1:$F$4,1+(COLUMNS($A:A)-1)/3,MOD(COLUMNS($A:A)-1,3)+1)
Many thanks to you.