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.

 

[Peter_SSs]

Welcome to the MrExcel board!

I've attached an image of how the data pulls out.

That is a little useful but would be better if
a) we were able to copy it for testing (see XL2BB)
b) we also knew what you wanted the results to look like.

 

[TrinaT]

Thanks!

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	First Name	Last Name	Item 1	Cost	Qty	Item 2	Cost	Qty	Item 3	Cost	Qty	Item 4	Cost	Qty
2	Joe	Blogs	Cooler	$10.00	1	Shirt	$60.00	1	Shirt 2	$40.00	1
3	Barry	English							Bag	$30.00	1
4	Dana	Drink				Cooler	$10.00	1	Shirt	$60.00	1	Shirt 2	$40.00	1
5
6
7
Data Sheet

I don't have a preference for what it looks like at the end, I really just need all the same items to be in the same column for easy collation and summary

 

[Peter_SSs]

Is a macro solution acceptable?
About how many different items would you expect altogether? (less than 10/ tens/ hundreds/ thousands/ ...)

 

[TrinaT]

Is a macro solution acceptable?
About how many different items would you expect altogether? (less than 10/ tens/ hundreds/ thousands/ ...)

Yes a macro is fine!

We are expecting about 250 total orders, with up to 7 products available.

 

[Peter_SSs]

with up to 7 products available.

7 products in total or 7 products per order which in theory could be 7 x 250 = 1750 products if all different?

 

[TrinaT]

7 products in total or 7 products per order which in theory could be 7 x 250 = 1750 products if all different?

Up to 7 products per order so yes in theory 1750.

 

[Peter_SSs]

Up to 7 products per order so yes in theory 1750.

Then I'm afraid that I will have to leave it to somebody else. For a relatively large number of products, the method that I would use (Windows machine) I believe is not available on a Mac and I don't have a Mac to test this or alternatives. Still, hopefully the extra information now available in the thread will assist any new helpers. Good luck!

 

[Alex Blakenburg]

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

 

[Fluff]

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.