Transpose several two-column groups of information into a single vertical two-column list

[Tiv]

I was sent a spreadsheet, but reading the information is difficult in a horizontal format. It needs to be vertical. I need to be able to transpose it, but I need it to be in a two-column formation.

The information came to me like this:

	Part	Qty	Part	Qty	Part	Qty	Part	Qty
Aisle 1	Bananas	1​	Oranges	2​	Apples	9​	Raspberries	50​
Aisle 2	Blueberries	16​	Mangos	7​	Potatoes	10​	Carrots	12​
Aisle 3	Green beans	72​	Lima beans	100​	Onions	37​	Celery	9​

I need it to look like this:

Aisle 1
Bananas	1​
Oranges	2​
Apples	9​
Raspberries	50​
Aisle 2
Blueberries	16​
Mangos	7​
Potatoes	10​
Carrots	12​
Aisle 3
Green beans	72​
Lima beans	100​
Onions	37​
Celery	9​

​

I'm not having any luck with the Transpose function unless there's more to the formula.

How do I turn several two-column-bits of information in a horizontal format into a vertical two-column list?

 

[Ezguy4u]

Tiv, Well let's get the ball rollin. Now I know you asked for a Transpose function, and I came up with a vba program. At least this should work, and we need to start some place. If you have any questions, comments or suggestions let us know. Now sometimes, the A plus students will weigh in and do the same thing this program does but they will do it with 2 lines of code. Anyway, let the discussion begin.

Sub Prog1()

Dim Row1 As Long
Dim Col1 As Long
Dim RowCnt As Long
Dim ColCnt As Long

Sheets("Complete").Cells.ClearContents

Sheets("Data").Select
Row1 = 2
Col1 = 1
a = 1

RowCnt = Cells(Rows.Count, "A").End(xlUp).Row
ColCnt = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("Complete").Cells(1, 1) = Cells(2, 1) '.Select

For i = 2 To RowCnt

For a = 2 To ColCnt

If Col1 = 1 Then
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = Col1 + 1
Else
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = 1
    Row1 = Row1 + 1
End If

Next a

Sheets("Complete").Cells(Row1 + 1, Col1) = Cells(i + 1, 1)
Row1 = Row1 + 2
Next i

Sheets("Complete").Select
Sheets("Complete").Range("A1").Select

End Sub

22-12-07 work 3.xlsm
	A	B
1	Aisle 1
2	Bananas	1
3	Oranges	2
4	Apples	9
5	Raspberries	50
6
7	Aisle 2
8	Blueberries	16
9	Mangos	7
10	Potatoes	10
11	Carrots	12
12
13	Aisle 3
14	Green beans	72
15	Lima beans	100
16	Onions	37
17	Celery	9
Complete

22-12-07 work 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Part	Qty	Part	Qty	Part	Qty	Part	Qty	Part
2	Aisle 1	Bananas	1	Oranges	2	Apples	9	Raspberries	50
3	Aisle 2	Blueberries	16	Mangos	7	Potatoes	10	Carrots	12
4	Aisle 3	Green beans	72	Lima beans	100	Onions	37	Celery	9
Data

 

[Tiv]

Tiv, Well let's get the ball rollin. Now I know you asked for a Transpose function, and I came up with a vba program. At least this should work, and we need to start some place. If you have any questions, comments or suggestions let us know. Now sometimes, the A plus students will weigh in and do the same thing this program does but they will do it with 2 lines of code. Anyway, let the discussion begin.

Sub Prog1()

Dim Row1 As Long
Dim Col1 As Long
Dim RowCnt As Long
Dim ColCnt As Long

Sheets("Complete").Cells.ClearContents

Sheets("Data").Select
Row1 = 2
Col1 = 1
a = 1

RowCnt = Cells(Rows.Count, "A").End(xlUp).Row
ColCnt = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("Complete").Cells(1, 1) = Cells(2, 1) '.Select

For i = 2 To RowCnt

For a = 2 To ColCnt

If Col1 = 1 Then
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = Col1 + 1
Else
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = 1
    Row1 = Row1 + 1
End If

Next a

Sheets("Complete").Cells(Row1 + 1, Col1) = Cells(i + 1, 1)
Row1 = Row1 + 2
Next i

Sheets("Complete").Select
Sheets("Complete").Range("A1").Select

End Sub

22-12-07 work 3.xlsm
	A	B
1	Aisle 1
2	Bananas	1
3	Oranges	2
4	Apples	9
5	Raspberries	50
6
7	Aisle 2
8	Blueberries	16
9	Mangos	7
10	Potatoes	10
11	Carrots	12
12
13	Aisle 3
14	Green beans	72
15	Lima beans	100
16	Onions	37
17	Celery	9
Complete

22-12-07 work 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Part	Qty	Part	Qty	Part	Qty	Part	Qty	Part
2	Aisle 1	Bananas	1	Oranges	2	Apples	9	Raspberries	50
3	Aisle 2	Blueberries	16	Mangos	7	Potatoes	10	Carrots	12
4	Aisle 3	Green beans	72	Lima beans	100	Onions	37	Celery	9
Data

Tiv, Well let's get the ball rollin. Now I know you asked for a Transpose function, and I came up with a vba program. At least this should work, and we need to start some place. If you have any questions, comments or suggestions let us know. Now sometimes, the A plus students will weigh in and do the same thing this program does but they will do it with 2 lines of code. Anyway, let the discussion begin.

Sub Prog1()

Dim Row1 As Long
Dim Col1 As Long
Dim RowCnt As Long
Dim ColCnt As Long

Sheets("Complete").Cells.ClearContents

Sheets("Data").Select
Row1 = 2
Col1 = 1
a = 1

RowCnt = Cells(Rows.Count, "A").End(xlUp).Row
ColCnt = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("Complete").Cells(1, 1) = Cells(2, 1) '.Select

For i = 2 To RowCnt

For a = 2 To ColCnt

If Col1 = 1 Then
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = Col1 + 1
Else
    Sheets("Complete").Cells(Row1, Col1) = Cells(i, a) '.Select
    Col1 = 1
    Row1 = Row1 + 1
End If

Next a

Sheets("Complete").Cells(Row1 + 1, Col1) = Cells(i + 1, 1)
Row1 = Row1 + 2
Next i

Sheets("Complete").Select
Sheets("Complete").Range("A1").Select

End Sub

22-12-07 work 3.xlsm
	A	B
1	Aisle 1
2	Bananas	1
3	Oranges	2
4	Apples	9
5	Raspberries	50
6
7	Aisle 2
8	Blueberries	16
9	Mangos	7
10	Potatoes	10
11	Carrots	12
12
13	Aisle 3
14	Green beans	72
15	Lima beans	100
16	Onions	37
17	Celery	9
Complete

22-12-07 work 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Part	Qty	Part	Qty	Part	Qty	Part	Qty	Part
2	Aisle 1	Bananas	1	Oranges	2	Apples	9	Raspberries	50
3	Aisle 2	Blueberries	16	Mangos	7	Potatoes	10	Carrots	12
4	Aisle 3	Green beans	72	Lima beans	100	Onions	37	Celery	9
Data

Thanks! I've got to learn VBA. That's a much more elegant solution to what I did.

I managed to mull it out, sort of, using an Xlookup formula. It ended up with the Aisle being at the top of the list, the part and the quantity being listed below. I'm going to try your solution to see how it works.

 

[Ezguy4u]

Tiv it is good to hear from you. The folks on this web site are very helpful. If you have any questions on how things work or what you need to do to make things work, just ask. My one suggestion is to download XL2BB software so you can work on your excel problems.

 

[alansidman]

If this presentation works for you then power query may be the answer

Column1	Value	Qty
Aisle 1	Bananas	1
Aisle 1	Oranges	2
Aisle 1	Apples	9
Aisle 1	Raspberries	50
Aisle 2	Blueberries	16
Aisle 2	Mangos	7
Aisle 2	Potatoes	10
Aisle 2	Carrots	12
Aisle 3	Green beans	72
Aisle 3	Lima beans	100
Aisle 3	Onions	37
Aisle 3	Celery	9

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Qty", each if Text.StartsWith([Attribute], "Qty") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Qty"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.StartsWith([Attribute], "Qty")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
in
    #"Removed Columns"

 

[Fluff]

I managed to mull it out, sort of, using an Xlookup formula.

Xlookup does not exist in xl2010, so is your profile correct?

 

[Tiv]

Tiv it is good to hear from you. The folks on this web site are very helpful. If you have any questions on how things work or what you need to do to make things work, just ask. My one suggestion is to download XL2BB software so you can work on your excel problems.

I've never heard of XL2BB software. What is it exactly?

 

[Tiv]

Xlookup does not exist in xl2010, so is your profile correct?

We've upgraded to Office 365 now. I've fallen in love with the Xlookup formula. I've always loved the Vlookup and the index/match, but I think I'm definitely a convert to the Xlookup function.

 

[Tiv]

If this presentation works for you then power query may be the answer

Column1	Value	Qty
Aisle 1	Bananas	1
Aisle 1	Oranges	2
Aisle 1	Apples	9
Aisle 1	Raspberries	50
Aisle 2	Blueberries	16
Aisle 2	Mangos	7
Aisle 2	Potatoes	10
Aisle 2	Carrots	12
Aisle 3	Green beans	72
Aisle 3	Lima beans	100
Aisle 3	Onions	37
Aisle 3	Celery	9

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Qty", each if Text.StartsWith([Attribute], "Qty") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Qty"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.StartsWith([Attribute], "Qty")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
in
    #"Removed Columns"

I've got to learn VBA. I've been using spreadsheets since Lotus 123, but I've never had a chance to learn VBA.

 

[Automatrix]

I've never heard of XL2BB software. What is it exactly?

An add-in that allows you to port Excel snippets easily into this message board (like alansidman did).

I will highly recommend going for alansidman's solution, as this structure allows you to do much more with your data later on. And it is based on Power Query, not VBA (and is much easier to learn).