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

 

[XOR LX]

Hi.

A VBA-based solution would certainly be more efficient here, though if you're interested in a formula-based solution and/or nobody comes along with that VBA, then perhaps you can adapt the solutions given here to meet your needs.

The first is to return the entries in a rows-first fashion, the second (which by the looks of it you would prefer) in a columns-first fashion.

Single column from many (containing blanks) (1)

Advanced Formula Challenge #1: Results and Discussion | EXCELXOR

Let me know if you have any problems.

Regards

 

[karti1986]

Hi.

A VBA-based solution would certainly be more efficient here, though if you're interested in a formula-based solution and/or nobody comes along with that VBA, then perhaps you can adapt the solutions given here to meet your needs.

The first is to return the entries in a rows-first fashion, the second (which by the looks of it you would prefer) in a columns-first fashion.

Single column from many (containing blanks) (1)

Advanced Formula Challenge #1: Results and Discussion | EXCELXOR

Let me know if you have any problems.

Regards

I'm open with anything mate have it VBA or Formula which makes it done.. thanks for the links.. will go through and see how it works.. thanks Mate!!

 

[karti1986]

Im still looking for a VBA Script since this is recurring and the values dynamic.. Any thing that works helps!!

 

[Snakehips]

karti986,


Assuming that you can manipulate your raw data so that you have a blank column A and raw data is starting in B1. (If not then code can be changed to suit your need)

Then run the below to compile your list in column A.

Sub KG ()Application.ScreenUpdating = False


lr = Cells(Rows.Count, 2).End(xlUp).Row
p = 0
For r = 1 To lr
c = Cells(r, Columns.Count).End(xlToLeft).Column
Range(Cells(r + p, 1), Cells(r + p + c - 2, 1)) = Application.Transpose(Range(Cells(r, 2), Cells(r, c)))
p = p + c - 2
Next r


End Sub

Hope that helps.

 

[Snakehips]

Just noticed that the code has rendered with a cr missing.

Should be...

Sub KD()


Application.ScreenUpdating = False


lr = Cells(Rows.Count, 2).End(xlUp).Row
p = 0
For r = 1 To lr
c = Cells(r, Columns.Count).End(xlToLeft).Column
Range(Cells(r + p, 1), Cells(r + p + c - 2, 1)) = Application.Transpose(Range(Cells(r, 2), Cells(r, c)))
p = p + c - 2
Next r


End Sub

 

[karti1986]

karti986,


Assuming that you can manipulate your raw data so that you have a blank column A and raw data is starting in B1. (If not then code can be changed to suit your need)

Then run the below to compile your list in column A.

Sub KG ()Application.ScreenUpdating = False


lr = Cells(Rows.Count, 2).End(xlUp).Row
p = 0
For r = 1 To lr
c = Cells(r, Columns.Count).End(xlToLeft).Column
Range(Cells(r + p, 1), Cells(r + p + c - 2, 1)) = Application.Transpose(Range(Cells(r, 2), Cells(r, c)))
p = p + c - 2
Next r


End Sub

Hope that helps.

Thanks a ton Snake,

That is closer to what I was looking for..
But the script, assembles the contents in Row 1 first and then Row 2 into Column A in a transpose manner.



What I am looking for would be is like

[TABLE="class: cms_table_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]


Where column A consists of Cells in Column B and Column C and so On.. I guess I made it clear.. please let me know your thoughts..

 

[gior]

Pls try in cell A3 (or first cell in Column A after your table) formula
=OFFSET( $A$1, MOD( ROW()-1, ROW()-ROW(A1)), INT( (ROW()-1)/( ROW()-ROW(A1))))

(Assuming your table starts from cell A1)

 

[Snakehips]

KG,

Where column A consists of Cells in Column B and Column C and so On.. I guess I made it clear.. please let me know your thoughts..

My first thought is that I'm a bit of a pillock

Your fist post makes it obvious but I obviously didn't read it properly and just went with the notion that you were wanting to transpose.

So, apologies.

Try this instead....

[/COLOR]Sub KD2()
Application.ScreenUpdating = False
Columns(1).ClearContents
 c = 2
Do Until Application.WorksheetFunction.CountA(Columns(c)) = 0
lrA = Cells(Rows.Count, 1).End(xlUp).Row
If lrA > 1 Then lrA = lrA + 1
lr2 = Cells(Rows.Count, c).End(xlUp).Row
Range(Cells(lrA, 1), Cells(lrA + lr2 - 1, 1)) = Range(Cells(1, c), Cells(lr2, c)).Value
c = c + 1
Loop
End Sub

Again requires a free column A and data in B2:/??

 

[karti1986]

KG,



My first thought is that I'm a bit of a pillock

Your fist post makes it obvious but I obviously didn't read it properly and just went with the notion that you were wanting to transpose.

So, apologies.

Try this instead....

[/COLOR]Sub KD2()
Application.ScreenUpdating = False
Columns(1).ClearContents
 c = 2
Do Until Application.WorksheetFunction.CountA(Columns(c)) = 0
lrA = Cells(Rows.Count, 1).End(xlUp).Row
If lrA > 1 Then lrA = lrA + 1
lr2 = Cells(Rows.Count, c).End(xlUp).Row
Range(Cells(lrA, 1), Cells(lrA + lr2 - 1, 1)) = Range(Cells(1, c), Cells(lr2, c)).Value
c = c + 1
Loop
End Sub

Again requires a free column A and data in B2:/??

Thanks Tony works like a charm..
Would you please help me by directing me to a source where I can learn Macros scripting
Appreciate your help!! Thanks Again!