Vlookup or?

[TrinaT]

I have a spreadsheet of data that is coming from an event registration software. Along with event registrations, people are able to purchase products. Unfortunately the data export pulls the product sales out in different columns (not the same column for each product), and displays in random order. It also pulls through blank columns randomly.

I'm trying to pull the data into a useable order so I can easily decipher who has ordered what, and pull out overall order quantities for the final orders.

I'm not even sure if this can be done. I've attached an image of how the data pulls out.

 

[TrinaT]

You have MacOS 365 which means you should have power query.
Are you happy to get it to this point (see below) ?
If you are then I can either give you the Power Query code or we can do it in VBA ?

20220323 Transpose Data TrinaT.xlsm
	A	B	C	D	E
13	First Name	Last Name	Item	Cost	Qty
14	Joe	Blogs	Cooler	10	1
15	Joe	Blogs	Shirt	60	1
16	Joe	Blogs	Shirt 2	40	1
17	Barry	English	Bag	30	1
18	Dana	Drink	Cooler	10	1
19	Dana	Drink	Shirt	60	1
20	Dana	Drink	Shirt 2	40	1
Sheet1

This is great! Yes that format is perfect!

 

[TrinaT]

To get a list of all items ordered & how many, you could use

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	First Name	Last Name	Item 1	Cost	Qty	Item 2	Cost	Qty	Item 3	Cost	Qty	Item 4	Cost	Qty	Item 5	Cost	Qty	Item 6	Cost	Qty	Item 7	Cost	Qty
2	Joe	Blogs	Cooler	10	1	Shirt	60	1	Shirt 2	40	1
3	Barry	English							Bag	30	1
4	Dana	Drink				Cooler	10	2	Shirt	60	1	Shirt 2	40	1
5
6
7
8
9
10	Cooler	3
11	Shirt	2
12	Shirt 2	2
13	Bag	1
14
Main

Cell Formulas
Range	Formula
A10:A13	A10	=LET(rng,C2:W8,r,ROWS(rng),s,SEQUENCE(r*COLUMNS(rng)/3,,0),i,INDEX(rng,MOD(s,r)+1,INT(s/r)*3+1),UNIQUE(FILTER(i,i<>"")))
B10:B13	B10	=SUMIFS(E2:W8,C2:U8,A10#)
Dynamic array formulas.

Awesome thanks!

 

[Alex Blakenburg]

Did you want to use Fluff's solution or did you still want to have a Power Query or VBA solution ?

 

[TrinaT]

Did you want to use Fluff's solution or did you still want to have a Power Query or VBA solution ?

Power Query for VBA would be great thanks Alex!

 

[Peter_SSs]

Awesome thanks!

Hmm, I thought you were after something quite different.

In your sample, for every order the Qty is 1 so it is not clear what the 'Cost' cells represent. If 2 Coolers were ordered, would the Cost still show 10 (cost per item) or would it be 20 (total cost)?

 

[TrinaT]

Hmm, I thought you were after something quite different.

In your sample, for every order the Qty is 1 so it is not clear what the 'Cost' cells represent. If 2 Coolers were ordered, would the Cost still show 10 or would it be 20?

Hi Peter, the cost would show $20

 

[Peter_SSs]

Hi Peter, the cost would show $20

Thanks. It looks like you might leaning away from a formula solution but if you have the latest dynamic array functions (not everybody with 365 does) then you can now get the item list more directly.

TrinaT.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	First Name	Last Name	Item 1	Cost	Qty	Item 2	Cost	Qty	Item 3	Cost	Qty	Item 4	Cost	Qty	Item 5	Cost	Qty	Item 6	Cost	Qty	Item 7	Cost	Qty
2	Joe	Blogs	Cooler	10	1	Shirt	60	1	Shirt 2	120	3
3	Barry	English							Bag	30	1
4	Dana	Drink				Cooler	20	2	Shirt	60	1	Shirt 2	40	1
5
9
10	Cooler	3	30
11	Shirt	2	120
12	Shirt 2	4	160
13	Bag	1	30
Sheet1 (2)

Cell Formulas
Range	Formula
A10:A13	A10	=UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN("|",1,IF(ISTEXT(C2:W4),C2:W4,"")),"|")))
B10:B13	B10	=SUMIFS(E2:W8,C2:U8,A25#)
C10:C13	C10	=SUMIFS(D2:V8,C2:U8,A25#)
Dynamic array formulas.

 

[Alex Blakenburg]

If you want to try using Power Query:

Start with converting your data into a table:
• Select any cell in your data range
• Either Insert > Table (3rd button from the left ) OR Ctrl + T
• Uncheck "My table has headers"

To make the table look like the original and not show Column 1, Column 2 etc, you can go to Table Design > Uncheck Header Row (about in the middle of the toolbar)

Then:
• Click on the tab Table Design and in the white box on the far left put in the table name tblSales

Now still with a cell in the Table Selected:

• Data > From Table Range
• in PQ > Home > Advanced Editor (3rd Button from the left
• Replace the code you find there with the code below
• Ideally give the query a meaningfull name (in the pane on the right)

Close & Load (will default to a new sheet) OR Close & Load To

let
    Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    AddedLineGrp = Table.AddColumn(#"Added Index", "LineGroup", each Number.IntegerDivide([Index],3)),
    AddedColGrp = Table.AddColumn(AddedLineGrp, "ColumnOrder", each Number.Mod([Index],3)),
    #"Filtered Rows" = Table.SelectRows(AddedColGrp, each ([Column2] <> "Last Name")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"ColumnOrder", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"ColumnOrder", type text}}, "en-AU")[ColumnOrder]), "ColumnOrder", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"LineGroup", Order.Ascending}}),
    #"Removed Columns LineGrp" = Table.RemoveColumns(#"Sorted Rows",{"LineGroup"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns LineGrp",{{"0", "Item"}, {"1", "Cost"}, {"2", "Qty"}, {"Column1", "First Name"}, {"Column2", "Last Name"}})
in
    #"Renamed Columns"

 

[TrinaT]

If you want to try using Power Query:

Start with converting your data into a table:
• Select any cell in your data range
• Either Insert > Table (3rd button from the left ) OR Ctrl + T
• Uncheck "My table has headers"

To make the table look like the original and not show Column 1, Column 2 etc, you can go to Table Design > Uncheck Header Row (about in the middle of the toolbar)

Then:
• Click on the tab Table Design and in the white box on the far left put in the table name tblSales

Now still with a cell in the Table Selected:

• Data > From Table Range
• in PQ > Home > Advanced Editor (3rd Button from the left
• Replace the code you find there with the code below
• Ideally give the query a meaningfull name (in the pane on the right)

Close & Load (will default to a new sheet) OR Close & Load To

let
    Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    AddedLineGrp = Table.AddColumn(#"Added Index", "LineGroup", each Number.IntegerDivide([Index],3)),
    AddedColGrp = Table.AddColumn(AddedLineGrp, "ColumnOrder", each Number.Mod([Index],3)),
    #"Filtered Rows" = Table.SelectRows(AddedColGrp, each ([Column2] <> "Last Name")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"ColumnOrder", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"ColumnOrder", type text}}, "en-AU")[ColumnOrder]), "ColumnOrder", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"LineGroup", Order.Ascending}}),
    #"Removed Columns LineGrp" = Table.RemoveColumns(#"Sorted Rows",{"LineGroup"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns LineGrp",{{"0", "Item"}, {"1", "Cost"}, {"2", "Qty"}, {"Column1", "First Name"}, {"Column2", "Last Name"}})
in
    #"Renamed Columns"

Thanks Alex, I don't have advanced editor on mac version, will swap to a Windows version later and complete from there

 

[Peter_SSs]

To rearrange the original data as in post #9 (no collating of similar people and/or items ordered) you could also consider this macro approach.

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A1").CurrentRegion.Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To 5)
  For i = 2 To UBound(a)
    For j = 3 To uba2 Step 3
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, j): b(k, 4) = a(i, j + 1): b(k, 5) = a(i, j + 2)
      End If
    Next j
  Next i
  Application.ScreenUpdating = False
  With Range("A" & UBound(a) + 4).Resize(k, 5)
    .Value = b
    .Rows(0).Value = Array("First Name", "Last Name", "Item", "Cost", "Qty")
  End With
  Application.ScreenUpdating = True
End Sub

So for original data as in rows 1:4 below, the code has produced the values in row 7 onwards.

TrinaT.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	First Name	Last Name	Item 1	Cost	Qty	Item 2	Cost	Qty	Item 3	Cost	Qty	Item 4	Cost	Qty	Item 5	Cost	Qty	Item 6	Cost	Qty	Item 7	Cost	Qty
2	Joe	Blogs	Cooler	10	1	Shirt	60	1	Shirt 2	120	3
3	Barry	English							Bag	30	1
4	Dana	Drink				Cooler	20	2	Shirt	60	1	Shirt 2	40	1
5
6
7	First Name	Last Name	Item	Cost	Qty
8	Joe	Blogs	Cooler	10	1
9	Joe	Blogs	Shirt	60	1
10	Joe	Blogs	Shirt 2	120	3
11	Barry	English	Bag	30	1
12	Dana	Drink	Cooler	20	2
13	Dana	Drink	Shirt	60	1
14	Dana	Drink	Shirt 2	40	1
Rearrange