# Convert Column and Rows....kinda

#### seriousallthetime

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### rpaulson

##### Well-known Member
VBA options.

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

then run this maco

Code:
``````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

Last edited:

#### sandy666

##### Well-known Member
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​

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

#### sandy666

##### Well-known Member
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​

Code:
``````[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

##### Well-known Member
Here's another attempt.
Code:
``````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``````

Replies
14
Views
108
Replies
1
Views
23
Replies
13
Views
108
Replies
4
Views
68
Replies
15
Views
203