Convert Column and Rows....kinda

seriousallthetime

New Member
Joined
Sep 20, 2018
Messages
5
This is what my data looks like
6063308260633006606330846063308060633046
1
9
2413
200120
321200
400011
523401

<tbody>
</tbody>


This is what I need it to look like:

1
606330829
2606330820
3606330822
4606330820
5606330822
1606330062
2606330060
3606330061
4606330060
5606330063
1606330844
2606330841
3606330842
4606330840
5606330844
1606330801
2606330802
3606330800
4606330801
5606330800
1606330463
2606330460
3606330460
4606330461
5606330461

<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.
 

Some videos you may like

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
Joined
Oct 4, 2007
Messages
1,106
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
Joined
Oct 24, 2015
Messages
6,740
with PowerQuery (Get&Transform)

6063308260633006606330846063308060633046Column1Value
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],
    Demote = Table.DemoteHeaders(Source),
    Transpose = Table.Transpose(Demote),
    Unpivot = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(Unpivot,{"Attribute"})
in
    RC[/SIZE]
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,740
Ops, I lost first column so here is

number6063308260633006606330846063308060633046numberAttributeValue
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
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top