Pulling a sequential list of cell data from the intersection of columns and rows

[Schopenhauer]

I've been trying to use INDEX and MATCH to get this done, but I can't create an output in the format I'm trying to accomplish.

Here's what I'm trying to do (apologies for the cruddy formatting):


Black White Red Orange Yellow

Chevy 200 400 500 390 122

Ford 400 300 450 700 500

Dodge 410 600 800 400 600

VW 600 400 775 210 450

Toyota 500 444 890 400 610


Here's what I'm trying to accomplish:


Output Desired:

Black Chevy 200
Black Ford 400
Black Dodge 410
Black VW 600
Black Toyota 500
White Chevy 400
White Ford 300
White Dodge 600
*
*
*
*
Yellow Dodge 600
Yellow VW 450
Yellow Toyota 610

Basically, I need a list that will compile all date in order, in one long column. Is this even possible? I've been researching this for a couple days now, and can't find anything that can quite get it to work. Maybe using HLOOKUP and MATCH? Not sure how to solve this one.

Thank you!

-Brad

 

[JLGWhiz]

See if this macro will work for you

Sub t()
Dim sh As Worksheet, i As Long, j As Long, lr As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    r = 2
    For i = 2 To 5
        For j = 2 To lr
            Sheets(2).Cells(r, 1) = sh.Cells(1, i)
            Sheets(2).Cells(r, 2) = sh.Cells(j, 1)
            Sheets(2).Cells(r, 3) = sh.Cells(j, i)
            r = r + 1
        Next
    Next
End Sub

 

[Schopenhauer]

That worked like a charm, JLG!! Thank you!!

 

[Rick Rothstein]

That worked like a charm, JLG!! Thank you!!

If you are interested, there is a formula solution that will also work. Put this formula in cell A8 and copy it down to cell A32...

=INDEX(B$1:F$1,1,1+INT((ROWS($8:8)-1)/5))&" "&INDEX(A$2:A$6,1+MOD(INT(ROWS($8:8)-1/5),5))&" "&INDEX(B$2:G$6,1+MOD(INT(ROWS($8:8)-1/5),5),1+MOD(INT((ROWS($8:8)-1)/5),5))

 

[JLGWhiz]

That worked like a charm, JLG!! Thank you!!

You're welcome,
regards, JLG