Convert Column and Rows....kinda

[seriousallthetime]

This is what my data looks like

	60633082	60633006	60633084	60633080	60633046
1	9	2	4	1	3
2	0	0	1	2	0
3	2	1	2	0	0
4	0	0	0	1	1
5	2	3	4	0	1

<tbody>
</tbody>

This is what I need it to look like:

1	60633082	9
2	60633082	0
3	60633082	2
4	60633082	0
5	60633082	2
1	60633006	2
2	60633006	0
3	60633006	1
4	60633006	0
5	60633006	3
1	60633084	4
2	60633084	1
3	60633084	2
4	60633084	0
5	60633084	4
1	60633080	1
2	60633080	2
3	60633080	0
4	60633080	1
5	60633080	0
1	60633046	3
2	60633046	0
3	60633046	0
4	60633046	1
5	60633046	1

<tbody>
</tbody>

Basically, the first sheet is store number on the first column, SKUs across the top, and the quantity to order in the middle.

The second is store, SKU, QTY.

The actual sheet has over 100 stores and about 45 SKUs, so you can see why I don't want to copy paste them twice a week for the foreseeable future.

It seems like an index and match of some sort would help, but I'm just not good enough at Excel to figure it out.

Thank you in advance for your help.

 

[rpaulson]

VBA options.

Create sheet and name it "results" without the quotes.

then run this maco

Sub do_it()

Dim ws As Worksheet
Set ws = Worksheets("results")

ws.Cells.ClearContents

For c = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
For r = 2 To Cells(Rows.Count, c).End(xlUp).Row

lr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1

ws.Cells(lr, "A") = Cells(r, 1)
ws.Cells(lr, "B") = Cells(1, c)
ws.Cells(lr, "C") = Cells(r, c)

Next r
Next c

ws.activate

End Sub

hth,
Ross

 

[sandy666]

with PowerQuery (Get&Transform)

60633082	60633006	60633084	60633080	60633046		Column1	Value
9​	2​	4​	1​	3​		60633082	9​
0​	0​	1​	2​	0​		60633082	0​
2​	1​	2​	0​	0​		60633082	2​
0​	0​	0​	1​	1​		60633082	0​
2​	3​	4​	0​	1​		60633082	2​
						60633006	2​
						60633006	0​
						60633006	1​
						60633006	0​
						60633006	3​
						60633084	4​
						60633084	1​
						60633084	2​
						60633084	0​
						60633084	4​
						60633080	1​
						60633080	2​
						60633080	0​
						60633080	1​
						60633080	0​
						60633046	3​
						60633046	0​
						60633046	0​
						60633046	1​
						60633046	1​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Transpose = Table.Transpose(Demote),
    Unpivot = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(Unpivot,{"Attribute"})
in
    RC[/SIZE]

 

[sandy666]

Ops, I lost first column so here is

number	60633082	60633006	60633084	60633080	60633046		number	Attribute	Value
1​	9​	2​	4​	1​	3​		1​	60633082	9​
2​	0​	0​	1​	2​	0​		2​	60633082	0​
3​	2​	1​	2​	0​	0​		3​	60633082	2​
4​	0​	0​	0​	1​	1​		4​	60633082	0​
5​	2​	3​	4​	0​	1​		5​	60633082	2​
							1​	60633006	2​
							2​	60633006	0​
							3​	60633006	1​
							4​	60633006	0​
							5​	60633006	3​
							1​	60633084	4​
							2​	60633084	1​
							3​	60633084	2​
							4​	60633084	0​
							5​	60633084	4​
							1​	60633080	1​
							2​	60633080	2​
							3​	60633080	0​
							4​	60633080	1​
							5​	60633080	0​
							1​	60633046	3​
							2​	60633046	0​
							3​	60633046	0​
							4​	60633046	1​
							5​	60633046	1​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    UnpivotSel = Table.Unpivot(Source, {"60633082", "60633006", "60633084", "60633080", "60633046"}, "Attribute", "Value"),
    Group = Table.Group(UnpivotSel, {"Attribute"}, {{"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Group, "Count", {"number", "Value"}, {"number", "Value"}),
    Reorder = Table.ReorderColumns(Expand,{"number", "Attribute", "Value"})
in
    Reorder[/SIZE]

 

[Norie]

Here's another attempt.

Sub DoStuff()
Dim arrIn As Variant
Dim arrOut()
Dim I As Long
Dim J As Long
Dim cnt As Long

    arrIn = Range("A1").CurrentRegion.Value

    ReDim arrOut(1 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1), 1 To 3)
    
    For J = 2 To UBound(arrIn, 2)
        For I = 2 To UBound(arrIn, 1)

            cnt = cnt + 1
            arrOut(cnt, 1) = arrIn(I, 1)
            arrOut(cnt, 2) = arrIn(1, J)

            arrOut(cnt, 3) = arrIn(I, J)
        Next I

    Next J

    Range("A1").Offset(, UBound(arrOut, 2) + 5).Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut

End Sub