Re arrange information

[yuvalshabt]

Hi all,
I need some help rearranging information I cant find a solution and I have TONS of this info to sort.

I have the following table that I want to sort in a specific way, but cant find a way to do so:

source:
Size\ model	D18292800	D178241	D189971
38​	2120292101​	1921369501​	2020611301​
40​		1921369502​	2020611302​
42​		1921369503​	2020611303​
44​	2120292104​	1921369504​
46​	2120292105​	1921369505​	2020611305​
48​			2020611306​
50​
Need:	model	size	sku
	D18292800	38​	2120292101​
	D18292800	44​	2120292104​
	D18292800	46​	2120292105​
	D178241	38​	1921369501​
	D178241	40​	1921369502​
	D178241	42​	1921369503​
	D178241	44​	1921369504​
	D178241	46​	1921369505​
	D189971	38​	2020611301​
	D189971	40​	2020611302​
	D189971	42​	2020611303​
	D189971	46​	2020611305​
	D189971	48​	2020611306​

 

[JEC]

One click in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    unPiv = Table.UnpivotOtherColumns(Source, {"Size\ model"}, "Model", "Value")
in
    unPiv

Book1
	A	B	C	D	E	F	G	H	I
1	Size\ model	D18292800	D178241	D189971			Size\ model	Model	Value
2	38	2120292101	1921369501	2020611301			38	D18292800	2120292101
3	40		1921369502	2020611302			38	D178241	1921369501
4	42		1921369503	2020611303			38	D189971	2020611301
5	44	2120292104	1921369504				40	D178241	1921369502
6	46	2120292105	1921369505	2020611305			40	D189971	2020611302
7	48			2020611306			42	D178241	1921369503
8	50						42	D189971	2020611303
9							44	D18292800	2120292104
10							44	D178241	1921369504
11							46	D18292800	2120292105
12							46	D178241	1921369505
13							46	D189971	2020611305
14							48	D189971	2020611306
Sheet1

 

[yuvalshabt]

I honestly dont know whats power query....
Thank you, Ill try to figure it out!!

 

[Fluff]

With a formula

Fluff.xlsm
	A	B	C	D
1	Size\ model	D18292800	D178241	D189971
2	38	2120292101	1921369501	2020611301
3	40		1921369502	2020611302
4	42		1921369503	2020611303
5	44	2120292104	1921369504
6	46	2120292105	1921369505	2020611305
7	48			2020611306
8	50
9
10
11
12	D18292800	38	2120292101
13	D18292800	44	2120292104
14	D18292800	46	2120292105
15	D178241	38	1921369501
16	D178241	40	1921369502
17	D178241	42	1921369503
18	D178241	44	1921369504
19	D178241	46	1921369505
20	D189971	38	2020611301
21	D189971	40	2020611302
22	D189971	42	2020611303
23	D189971	46	2020611305
24	D189971	48	2020611306
25
Data

Cell Formulas
Range	Formula
A12:C24	A12	=LET(d,B2:D8,r,ROWS(d),c,COLUMNS(d),s,SEQUENCE(r*c,,0),x,INDEX(B1:D1,INT(s/r)+1),y,INDEX(A2:A8,MOD(s,r)+1),z,INDEX(d,MOD(s,r)+1,INT(s/r)+1),aa,CHOOSE({1,2,3},x,y,z),FILTER(aa,INDEX(aa,,3)<>0))
Dynamic array formulas.

 

[yuvalshabt]

With a formula

Fluff.xlsm
	A	B	C	D
1	Size\ model	D18292800	D178241	D189971
2	38	2120292101	1921369501	2020611301
3	40		1921369502	2020611302
4	42		1921369503	2020611303
5	44	2120292104	1921369504
6	46	2120292105	1921369505	2020611305
7	48			2020611306
8	50
9
10
11
12	D18292800	38	2120292101
13	D18292800	44	2120292104
14	D18292800	46	2120292105
15	D178241	38	1921369501
16	D178241	40	1921369502
17	D178241	42	1921369503
18	D178241	44	1921369504
19	D178241	46	1921369505
20	D189971	38	2020611301
21	D189971	40	2020611302
22	D189971	42	2020611303
23	D189971	46	2020611305
24	D189971	48	2020611306
25
Data

Cell Formulas
Range	Formula
A12:C24	A12	=LET(d,B2:D8,r,ROWS(d),c,COLUMNS(d),s,SEQUENCE(r*c,,0),x,INDEX(B1:D1,INT(s/r)+1),y,INDEX(A2:A8,MOD(s,r)+1),z,INDEX(d,MOD(s,r)+1,INT(s/r)+1),aa,CHOOSE({1,2,3},x,y,z),FILTER(aa,INDEX(aa,,3)<>0))
Dynamic array formulas.

Wow, amazing! Thank you very much!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mumps]

VBA approach. Change the sheet names (in red) to suit your needs.

Sub RearrangeData()
    Application.ScreenUpdating = False
    Dim lRow As Long, lCol As Long, arr() As Variant, arr2() As Variant, v As Variant, r As Long, c As Long, x As Long
    Dim srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    desWS.Range("A1").Resize(, 3).Value = Array("Model", "Size", "SKU")
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = srcWS.Cells(2, srcWS.Columns.Count).End(xlToLeft).Column
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, lCol).Value
    For c = LBound(v, 2) + 1 To UBound(v, 2)
        For r = LBound(v) + 1 To UBound(v)
            If v(r, c) <> "" Then
                x = x + 1
                ReDim Preserve arr(1 To x)
                ReDim Preserve arr2(1 To x)
                arr(x) = v(r, 1)
                arr2(x) = v(r, c)
            End If
        Next r
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(x) = v(1, c)
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(x) = Application.Transpose(arr)
            .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(x) = Application.Transpose(arr2)
        End With
        x = 0
    Next c
    Application.ScreenUpdating = True
End Sub

 

[alansidman]

FYI

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.