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.

 

[Fluff]

if you have the latest dynamic array functions

I don't
I assumed that TEXTSPLIT, HSTACK etc where only on the Insider Channel.

 

[TrinaT]

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

Thanks Guys, I used Alex's PQ and that has worked. If I then wanted to pull the cleaned info into a table (qty ordered next to each person) what would the best formula be to use?

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	First Name	Last Name	Item	Cost	Qty
2	John	Doe	Cooler Bag	10	1
3	John	Doe	T-Shirt - Ladies (V-Neck)	40	1
4	John	Doe	T-Shirt - Mens	40	1
5	John	Doe	Event - Adult Registration	165	1
6	Daisy	Duke	Shirt - Long Sleeve	60	1
7	Daisy	Duke	T-Shirt - Mens	40	1
8	Daisy	Duke	Event - Adult Registration	165	1
9	Daisy	Duke	Event) - Child Registration (under 12 years old)	100	1
10	Barry	Manilo	Cooler Bag	10	1
11	Barry	Manilo	Shirt - Long Sleeve	60	1
12	Barry	Manilo	T-Shirt - Mens	40	1
13	Barry	Manilo	Event - Adult Registration	165	1
14	Mark	Jones	Event - Adult Registration	165	1
15	Adam	Ant	Cooler Bag	10	1
16	Adam	Ant	Shirt - Long Sleeve	60	1
17	Adam	Ant	T-Shirt - Ladies (V-Neck)	40	1
18	Adam	Ant	Event - Adult Registration	165	1
19
20
21
22
23	First Name	Last Name	Registrations Adult	Registrations Child	T-Shirt Mens	Qty	Colour	Size	T-Shirt - Ladies (V-Neck)	Qty	Colour	Size	Shirt - Long Sleeve	Qty	Colour	Size	Hooded Sweat	Qty	Colour	Size	Mascot	Qty	Cooler Bag	Qty	Bar Runner	Qty
24
25
26
27
Data Sheet

 

[Alex Blakenburg]

Its not really clear what you mean, are you saying there is a second data extract ?
It that what you are showing at row 23 ? Can you provide some sample data ?
What is the link between the first table and the 2nd table eg first table has First/Last Name & Product as the lowest possible combination to link on ?

Can you show us what you expect the output to look like using the sample data you provide ?