A more efficient approach to my data cleansing task

[Yamezz]

I have some exported files to work with, collating and summing data for individual SKUs.
A PaymentType file has details of the type of payment(s) made for each order number. Most order numbers have just one payment. The odd one has multiple payments applied to the one order.
Another file has the OrderDetails (SKUs and prices) that constitute those orders. For those orders with just one payment, it's simple to sum the SKUs, however, for the orders that have multiple payments applied, the OrderDetails file records multiple instances of the SKUs (on a new line). If I sold one Apple in an order with one payment, then only one Apple shows in the OrderDetails file, but if three part-payments were made for an order for one Orange, then the OrderDetails file shows three records for one Orange, with the same order number.

i.e.

Order	SKU	Quantity
#1135	Apple	1
#1136	Orange	1
#1136	Orange	1
#1136	Orange	1

The only way I know how to accomplish correcting the summed data is as follows:
Use Application.WorksheetFunction.CountIF to examine the PaymentType file for duplicates and create a temporary table with the order number and number of payments.
Step through each line of the OrderDetails file and use Application.WorksheetFunction.VLookup to check each order number against the temporary table and then divide the values in the SKU column by the number of instances in the temporary table. This would mean, in the example above, I would get 1 Apple/1 occurance = 1 Apple; and each of the three lines for Orange would be changed to 1/3 Orange, so the final sum (with later code) would be 3 * 1/3 = 1 Orange.

Is there a more efficient way for me to achieve the same result?

 

[StephenCrump]

Perhaps something like this?

	A	B	C	D	E	F
1	Order	SKU	Quantity		No orders	2
2	#1135	Apple	4
3	#1135	Apple	4		Totals
4	#1135	Orange	2		Apple	4
5	#1135	Orange	2		Banana	7
6	#1135	Banana	7		Orange	10
7	#1135	Banana	7
8	#1136	Orange	8
9	#1136	Orange	8
10	#1136	Orange	8
Sheet1

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
F4:F6	F4	=SUMPRODUCT(C$2:C$10,(B$2:B$10=E4)/COUNTIFS(A$2:A$10,A$2:A$10,B$2:B$10,B$2:B$10))

(But this doesn't sound like the ideal way to calculate quantities. If the payments data varies in any way from what you've described, you may get incorrect results)

 

[Yamezz]

Thanks Stephen.

I've tried your formulas on some actual data and have identified an instance of an incorrect result.

Shopify to Xero.xlsm
	A	B	C	D	E	F	G	H
1	Order	SKU	Quantity	Price		No orders	6
2	#1133	Apple	1	5.5
3	#1134	Banana	1	30		SKU	Totals	Should Be
4	#1134	Cherry	1	42		Apple	1	1
5	#1134	Durian	2	15		Banana	1	1
6	#1134	Elderberry	1	30		Cherry	3	3
7	#1135	Fig	1	35		Durian	2	2
8	#1136	Cherry	2	42		Elderberry	1	1
9	#1136	Cherry	2	42		Fig	2	2
10	#1136	Grape	2	12		Grape	3.5	7
11	#1136	Grape	2	12		Honeydew	1	1
12	#1136	Honeydew	1	10		Kiwi	2	2
13	#1136	Honeydew	1	10		Lemon	2	2
14	#1136	Kiwi	2	30		Mango	1	1
15	#1136	Kiwi	2	30
16	#1136	Lemon	2	10
17	#1136	Lemon	2	10
18	#1136	Grape	5	12
19	#1136	Grape	5	12
20	#1137	Mango	1	22
21	#1138	Fig	1	35
Sheet2

Cell Formulas
Range	Formula
G1	G1	=SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21))
G4:G14	G4	=SUMPRODUCT(C$2:C$21,(B$2:B$21=F4)/COUNTIFS(A$2:A$21,A$2:A$21,B$2:B$21,B$2:B$21))

In this data, order #1136 was paid in two payments, hence the troublesome doubling up of each line. I have no idea why the output I'm dealing with lists two more instances of Grape (rather than two identical lines with Grape - 7). When looking at Grape, the logic should effectively be row 11 is a duplicate of row 10, and Row 19 is a duplicate of row 18, hence the total is 2 + 5 = 7.

Do you think there's a way to code for this ugly data I have to deal with?

 

[StephenCrump]

Do you think there's a way to code for this ugly data I have to deal with?

Ha ha, hence my comment:

(But this doesn't sound like the ideal way to calculate quantities. If the payments data varies in any way from what you've described, you may get incorrect results)

With this small example, it's obvious by inspection that the answer should be 2 + 5 = 7 grapes, because there are other SKUs in #1136, all duplicated.

But it's not quite as easy to code in a formula. What if the second occurrence was also 2 grapes, rather than 5? How does the formula determine that this is two occurrences, paid twice, vs one occurrence paid four times?

And I'm sure there are many other ways your data will not behave. if I was checking this, I'd be sorting and analysing in some detail to identify any data anomalies. VBA would be useful if this was something that needed to be done regularly. But you'll never have complete certainty, e.g. is the answer 2 or 4 for ... ?

Order	SKU	Quantity	Price
#1133	Apple	2	11
#1133	Apple	2	11

I'm curious, why are you calculating SKU numbers using payment data, rather than order data?

Payment data is all about $ - how much have we been paid, how much is due / overdue etc.

The question you're answering is - out of this list of payments, how many apples/oranges were in the orders, and hence how many have been paid for or part paid for? Why would anyone need to know this?

 

[Yamezz]

The intent of all this is to form part of a rather large macro taking Shopify data and cleansing/collating it into a format that the Xero accounting software can read.
I had it all working fine until I realised that some customers had split the bill, so we were ending up with doubled up values where an order# was appearing twice.

My original idea (in post 1) would hopefully take care of the anomalies like #1136 - by counting how many times the order was paid and dividing all rows of data by that count. So for #1136, the Application.WorksheetFunction.SumIFs code I've already written would sum 1,1,2.5,2.5 to get the correct total of 7 Grapes.

Unfortunately no one report out of Shopify contains all the data I need, so I'm working with 3 different reports to do this. Hence why I was hoping there would be a more efficient approach than mine suggested in post #1, which involves temporary lists and calculations for every row of data.