Multiple columns of data on 1 row combined into multiple rows for identical value

[jamobe]

Hi

Apologies if I have worded this wrong, I couldn't seem to find any help on the net regarding this method.

Basically I have 1 row of data as shown below

I would like to be able to have it transposed as below

I have many other columns inbetween the sku and Min Max prices, so not really after a formula that can transpose. I have attempted using the power query editor but I cannot get it to do as needed. I would like to be able to use the power query method rather than using VBA which is what the majority of posts I have stumbled upon recommend. So any help or advice on where I can learn how to apply this to my data would be great. Many Thanks!

 

[Saurabhj]

Hi, any reason of not using VBA. That will be an easy approach.

 

[Saba Sabaratnam]

Hi



My table layout is shown below.

Load into Power Query Editor as shown below.

Remove unwanted columns 1 and 2 and the result is shown below.

Merge Min Price 1 and Max Price 1 with a delimiter

Repeat the process for other columns

Select Column SKU and Unpivot all Other Columns by going to Unpivot and select Other Columns options.

Remove Attribute column and split value column into two and rename the two columns.

Then load into your desired output location in your workbook.

Kind regards



Saba

 

[lrobbo314]

Another way using Power Query.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SKU	Column1	Column2	Min Price1	Max Price1	Min Price2	Max Price2	Min Price3	Max Price3		SKU	Min Price	Max Price
2	SKU1	SS	AA	5	10	6	12	7	12		SKU1	5	10
3	SKU2	SS	AA	4	8	7	13	8	13		SKU1	6	12
4	SKU3	SS	AA	3	5	4	8	4	8		SKU1	7	12
5											SKU2	4	8
6											SKU2	7	13
7											SKU2	8	13
8											SKU3	3	5
9											SKU3	4	8
10											SKU3	4	8
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RCC = Table.RemoveColumns(Source,{"Column1", "Column2"}),
    Unpivot = Table.UnpivotOtherColumns(RCC, {"SKU"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute",each Text.Select(_,{"A".."Z","a".."z"," "})}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    Int = Table.TransformColumns(Index,{{"Index", each Number.IntegerDivide(_,2)}}),
    Pivot = Table.Pivot(Int, List.Distinct(Int[Attribute]), "Attribute", "Value"),
    RC = Table.RemoveColumns(Pivot,{"Index"})
in
    RC

 

[jamobe]

Thank you guys this is perfect!!

 

[jamobe]

Hi, any reason of not using VBA. That will be an easy approach.

Hi, I feel slightly more comfortable using power query for something like this, if I add or remove columns, any issues it would be easy to notice, if there is a way you can reference each column in VBA (not using a number) so if I moved any columns VBA would still pick up the correct data then I'd be very interested in that. As far as my VBA goes, I can only ever reference columns in a table by it's number ie .cells (1,20) I have struggled to find any way where I can simply use .cells (1, (Min Price)) basically just referencing the column header. Also, if using VBA to get this data onto a sheet, does it update automatically? Or does it require a refresh?

 

[lrobbo314]

This seems to do the trick with VBA and it updates automatically.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SKU	Column1	Column2	Min Price1	Max Price1	Min Price2	Max Price2	Min Price3	Max Price3			SKU	Min Price	Max Price
2	SKU1	SS	AA	5	10	6	12	7	12			SKU1	5	10
3	SKU2	SS	AA	4	8	7	13	8	13			SKU1	6	12
4	SKU3	SS	AA	3	5	4	8	4	8			SKU1	7	12
5	SKU4	SS	AA	5	12	4	15	3	9			SKU2	4	8
6	SKU5	SS	AA	4	15	2	8	5	17			SKU2	7	13
7												SKU2	8	13
8												SKU3	3	5
9												SKU3	4	8
10												SKU3	4	8
11												SKU4	5	12
12												SKU4	4	15
13												SKU4	3	9
14												SKU5	4	15
15												SKU5	2	8
16												SKU5	5	17
Sheet4

Standard Module

Sub SKU()
Dim r As Range:         Set r = Range("A2:I" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For ro = 1 To UBound(AR)
        For col = 4 To UBound(AR, 2) Step 2
            If Not IsNull(AR(ro, col)) And Not IsNull(AR(ro, col + 1)) Then
                .Add Join(Array(AR(ro, 1), AR(ro, col), AR(ro, col + 1)), ";")
            End If
        Next col
    Next ro
    
    Range("L1").CurrentRegion.Clear
    Set r = Range("L1:N1")
    r.Value2 = Array("SKU", "Min Price", "Max Price")
    Set r = r.Offset(1).Resize(.Count, 1)
    r.Value = Application.Transpose(.toarray)
    r.TextToColumns DataType:=xlDelimited, Semicolon:=True
End With

End Sub

Worksheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
Dim r As Range: Set r = Range("D" & LR & ":" & "I" & LR)
If Not Intersect(Target, r) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        SKU
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End If
End Sub

 

[lrobbo314]

If you have Excel 365 you can use dynamic array formulas.

Learning Sequences.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SKU	Column1	Column2	Min Price1	Max Price1	Min Price2	Max Price2	Min Price3	Max Price3		SKU	Min Price	Max Price
2	SKU1	SS	AA	5	10	6	12	7	12		SKU1	5	10
3	SKU2	SS	AA	4	8	7	13	8	13		SKU1	6	12
4	SKU3	SS	AA	3	5	4	8	4	8		SKU1	7	12
5	SKU4	SS	AA	5	12	4	15	3	9		SKU2	4	8
6	SKU5	SS	AA	4	15	2	8	5	17		SKU2	7	13
7											SKU2	8	13
8											SKU3	3	5
9											SKU3	4	8
10											SKU3	4	8
11											SKU4	5	12
12											SKU4	4	15
13											SKU4	3	9
14											SKU5	4	15
15											SKU5	2	8
16											SKU5	5	17
Sheet4

Cell Formulas
Range	Formula
K2:M16	K2	=LET(tbl,Table1[[Min Price1]:[Max Price3]],r,ROWS(tbl), seq,SEQUENCE((r*COLUMNS(tbl))/2,,0), rs,INT((seq)/(3))+1, cs,((MOD(seq,3)+1)*2-2)+SEQUENCE(,2), u,INDEX(tbl,rs,cs),sk,Table1[SKU], ss,INDEX(sk,rs), xt,CHOOSE({1,1,2},u,ss), INDEX(xt,seq+1,{3,1,2}))
Dynamic array formulas.