Moving data in columns to one large row list?

[hoff123]

Hi All,

I have been copy/pasting for hours now and thought there has to be a quicker way, so here I am to seek some advice. I have data that looks like this:

A1 - Title 1, B1 - Title 2, C1 - Title 3
A2 - Data 1, B2 - Data 2, C2 - Data 3
A3 - Data 1, B3 - Data 2, C3 - Data 3
A4 - Data 1, B4 - Data 2, C4 - Data 4

What I need is for it to look like the following:

A1 - Data 1, B1 - Title 1
A2 - Data 1, B2 - Title 1
A3 - Data 1, B2 - Title 1
A4 - Data 1, B2 - Title 1
A5 - Data 2, B1 - Title 2
A6 - Data 2, B2 - Title 2
A7 - Data 2, B2 - Title 2
A8 - Data 2, B2 - Title 2

So I guess it's a little bit like a transpose, but for multiple columns. Is there a way to do this with a formula of some sort? In the end, I have a stack of columns that I need to move into one list and then reference the title in the following column.

Thanks in advance for your help!

 

[cgcamal]

Hi hoff123,

Try with:

Excel Workbook
	A	B	C
1	Title1	Title2	Title3
2	Data 1	Data 2	Data 3
3	Data 1	Data 2	Data 3
4	Data 1	Data 2	Data 3
Sheet1

Insert the formulas in D2 and E2 and Copy down.

Excel Workbook
	D	E
2	Data 1	Title1
3	Data 1	Title1
4	Data 1	Title1
5	Data 2	Title2
6	Data 2	Title2
7	Data 2	Title2
8	Data 3	Title3
9	Data 3	Title3
10	Data 3	Title3
Sheet1

Cell Formulas
Range	Formula
D2	=IF(ROW(A2)<=COUNTA(A:A),A2,IF(ROW(B2)<=2*COUNTA(A:A)-1,INDIRECT(ADDRESS(ROW(B2)-COUNTA(A:A)+1,2)),IF(ROW(C2)<=3*COUNTA(A:A)-2,INDIRECT(ADDRESS(ROW(C2)-2*COUNTA(A:A)+2,3)),"")))
E2	=IF(ROW(A2)<=COUNTA(A:A),$A$1,IF(ROW(B2)<=2*COUNTA(A:A)-1,$B$1,IF(ROW(C2)<=3*COUNTA(A:A)-2,$C$1,"")))

Hope this helps.

Regards

 

[hoff123]

Hi Cesar,

This is perfect! One more quick question..

I have over 80 columns I need to get into one column. I've tried editing your formula to work for, say, column D, however I get a result of "0" in Excel. As an example of my attempt to add to your formula:

=IF(ROW(A2)<=COUNTA(A:A),A2,IF(ROW(B2)<=2*COUNTA(A:A)-1,INDIRECT(ADDRESS(ROW(B2)-COUNTA(A:A)+1,2)),IF(ROW(C2)<=3*COUNTA(A:A)-2,INDIRECT(ADDRESS(ROW(C2)-2*COUNTA(A:A)+2,3)),IF(ROW(D2)<=4*COUNTA(A:A)-3,INDIRECT(ADDRESS(ROW(D2)-3*COUNTA(A:A)+3,4)),"")))

Where have I gone wrong?

 

[cgcamal]

Hi hoff,

You have 80 columns, but you need in one column all 80 columns or only 4 columns?

May you show your sample file please?

If you need all 80 columns in a single one, maybe it could be needed to do with a macro.

Regards

 

[Norie]

If I understand correctly this will do what you want.

Option Explicit
 
Sub MoveData()
Dim rngSrc As Range
Dim rngDst As Range
Dim NoRows As Long
 
    Set rngSrc = Worksheets("Sheet1").Range("A1")
 
    Set rngDst = Worksheets("Sheet2").Range("A1")
 
    While rngSrc.Value <> ""
 
        NoRows = Worksheets("Sheet1").Cells(Rows.Count, rngSrc.Column).End(xlUp).Row - 1
 
        rngSrc.Offset(1).Resize(NoRows).Copy rngDst.Offset(, 1)
 
        rngSrc.Copy rngDst.Resize(NoRows)
 
        Set rngDst = rngDst.Offset(NoRows)
 
        Set rngSrc = rngSrc.Offset(, 1)
 
    Wend
 
End Sub

You'll probably need/want to change the worksheet names etc, I just used simple examples and it puts the reorganized data on a different worksheet.

 

[cgcamal]

Hoff,

To expand the formula to 4 columns I've written in different lines to see more clear the logic as follow
(I've removed ADDRESS function, since changing the expression within "INDIRECT" we can do it directly):

=IF(ROW(A2)<=COUNTA(A:A),A2,
IF(ROW(B2)<=2*COUNTA(A:A)-1,INDIRECT("B"&ROW(B2)-COUNTA(A:A)+1),
IF(ROW(C2)<=3*COUNTA(A:A)-2,INDIRECT("C"&ROW(C2)-2*COUNTA(A:A)+2),
IF(ROW(C2)<=4*COUNTA(A:A)-3,INDIRECT("D"&ROW(D2)-3*COUNTA(A:A)+3),""))))

and for the headers:

=IF(ROW(A3)<=COUNTA(A:A),$A$1,
IF(ROW(B3)<=2*COUNTA(A:A)-1,$B$1,
IF(ROW(C3)<=3*COUNTA(A:A)-2,$C$1,
IF(ROW(D3)<=4*COUNTA(A:A)-3,$D$1,""))))

Regards