Converting multiple rows into single column

[karti1986]

Hi Folks

I have a requirement where multiple rows to be placed in a single column one below another. The Rows&columns are dynamic(need not be the same number of rows and columns). Is there anyway we can do this with the help of a Macro/Formula? with my current knowledge its apart from my reach.. Appreciate any help!! thanks in advance.

raw dump:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD]Mike[/TD]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Tom[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

expected Format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John
[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[/TR]
</tbody>[/TABLE]

Regards
KG

 

[Snakehips]

KG,

You are welcome.

If you have $30 to spare then this downloadable video series by Bill Jelen (MrExcel) will give you a great basis upon which to get started.
Excel VBA and Macros with MrExcel LiveLessons (Video Training), (Downloadable Video) | Que

There is an absolute wealth of stuff out there in all forms.
Here is a link to a resource list compiled by regular forum contributor, hiker95.
You will find it in post#7

'Hiker's List'

Hope that helps.

 

[Mike Szczesny]

Hi Folks

I have a requirement where multiple rows to be placed in a single column one below another. The Rows&columns are dynamic(need not be the same number of rows and columns). Is there anyway we can do this with the help of a Macro/Formula? with my current knowledge its apart from my reach.. Appreciate any help!! thanks in advance.

raw dump:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD]Mike[/TD]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Tom[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

expected Format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[/TR]
</tbody>[/TABLE]

Regards
KG

Assume your source data range is A1:B2. Here is your formula. Use Cntrl-
Shift-Enter. Enter formula in A4 copy down

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($A$1:$B$2,MOD(SMALL(IF($A$1:$B$2<>"",(COLUMN($A$1:$B$2)-COLUMN($A$1)+1)*10^9+ROW($A$1:$B$2)-ROW($A$1)+1),ROWS($A$4:A4)),10^9),INT(SMALL(IF($A$1:$B$2<>"",(COLUMN($A$1:$B$2)-COLUMN($A$1)+1)*10^9+ROW($A$1:$B$2)-ROW($A$1)+1),ROWS($A$4:A4))/10^9))[/TD]
[/TR]
</tbody>[/TABLE]