Values in columns to rows

[Pattyboy]

Hello,

I have a question about converting data in multiple columns and rows into a single row for each cell. I'm wondering if anyone knows of a macro or Excel formula that could accomplish this. I'll try to provide an example below.

As Is:
Column 1 Column 2 Column 3
Row 1: Test1 Test2
Row 2: Test3
Row 3: Test4 Test5 Test6
Row 4: Test7 Test8

To Be:
Column 1
Row 1: Test1
Row 2: Test2
Row 3: Test3
Row 4: Test4
Row 5: Test5
Row 6: Test6
Row 7: Test7
Row 8: Test8

Any assistance would be greatly appreciated!

 

[Domenic]

As I've already mentioned, this is a great place to learn. Look at the solutions offered by others, and ask questions when there's something you don't understand. The more time you spend here (or other forums dedicated to Excel), the more you'll learn. And, of course, there are countless books available on Excel.

 

[mahmed1]

As I've already mentioned, this is a great place to learn. Look at the solutions offered by others, and ask questions when there's something you don't understand. The more time you spend here (or other forums dedicated to Excel), the more you'll learn. And, of course, there are countless books available on Excel.

Thank you

 

[mahmed1]

Assuming that A1:C4 contains the data, and that the data contains text values, try...

E1:

=COUNTIF(A1:C4,"?*")

F1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(F$1:F1)<=$E$1,INDEX($A$1:$C$4,SMALL(IF($A$1:$C$4<>"",ROW($A$1:$C$4)-ROW($A$1)+1),ROWS(F$1:F1)),RIGHT(SMALL(IF($A$1:$C$4<>"",(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)),ROWS($F$1:F1)),5)+0),"")

Hi, i have noticed that the Countif does not take into consideration if there is just numbers in the cell, how do i get around that?

Thanks

 

[Domenic]

If the data contains numerical values...

=COUNT(A1:C4)

If the data can contain both text and numerical values...

=COUNTA(A1:C4)-COUNTBLANK(A1:C4)

or

=SUMPRODUCT(--(A1:C4<>""))

 

[snb_]

I'd prefer VBA:

Sub tst()
    For j = 2 To Sheets(1).UsedRange.Columns.Count
        Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Sheets(1).UsedRange.Rows.Count) = Sheets(1).UsedRange.Columns(j).Value
    Next
End Sub