Clear cell data if rows match

[muhleebbin]

Hi,

I have a data set from my website of customer orders, however I need to manipulate it so that it omits duplicate entries which is throwing off the pivot table calculations.

Basically column A has the order numbers and there are often times duplicate entries, column O has the amount paid but it is the same for both (sometimes multiple rows) so it accounts for the amount twice (or however many times it is repeated. Is it possible to make a macro that finds matching order numbers and then only keeping the first instance of the amount in Column O?

Hope this makes sense and thank you in advance for your help!

 

[mrshl9898]

Sounds like you just need to remove duplicates.

Sub RemoveDuplicates()
    ActiveSheet.Range("$A:$O").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[muhleebbin]

Sounds like you just need to remove duplicates.

Sub RemoveDuplicates()
    ActiveSheet.Range("$A:$O").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

There are a few instances where a duplicate is needed. Typically this would be the solution but there are a few instances where the customer purchases something for one day at one price and another day for another price but on the same transaction (i.e. $50 for June 4 $100 for June 5 for a total of $150). We still need to see the two individual transactions. But column O records $150 for each row so the pivottable thinks the amount collected is $300 rather than the true $150.

 

[mrshl9898]

So remove duplicates by date and column A? Which column contains the date?

 

[muhleebbin]

Date is in Column D

Attached is some sample data. Sample Test and Test1-2 it would be fine to delete the extra rows but I'd prefer to keep the row and just remove the duplicate entry in column o. Test 3&4 are the cases where it would be ideal to keep the rows. As you can see in column U the unit price is different for each class that was registered for. That information is actually valuable for us.

Thanks!

ItemsSold_5-1-2021_to_5-31-2021 (1).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	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1	Order Id	Reference Id	DATE	Date Only	Time Only	email	Order Sub Total	Shipping Total	Tax Total	Shipping Tax Total	Order Items Tax	Order Discount Total	Grand Total	Gift Card Paid	Amount Paid	Card Type	Gift Card Used	Product	Category Name	Quantity	Unit Price	Total Item Price	Item Shipping Total	Item Tax Total	Item Discount	Item Grand Total	Event Date	Shipping Name	Shipping Address Line1	Shipping Address Line2	Shipping City	Shipping State	Shipping Postal Code	Shipping Phone	Billing Name	Billing Address Line1	Billing Address Line2	Billing City	Billing State	Billing Postal Code	Billing Phone	Gift Card Number	Series Name	From Name	To Name	To Email	Ship Method	CC Last Four	Note
2	1207969	Test	05/30/2021 15:16:45 -07:00	########	15:16:45.2429765	Test	170	0	0	0	0	0	170	0	170	Visa		Spring Gnocchi | In Person Class	EventVouchers	1	85	85	0	0	0	85	06/18/2021 18:00:00 -07:00								Test	Test		Test	Test	Test	Test	Test	Hot Stove Society Class Voucher		Test	Test		Test
3	1207969	Test	05/30/2021 15:16:45 -07:00	########	15:16:45.2429765	Test	170	0	0	0	0	0	170	0	170	Visa		Spring Gnocchi | In Person Class	EventVouchers	1	85	85	0	0	0	85	06/18/2021 18:00:00 -07:00								Test	Test		Test	Test	Test	Test	Test	Hot Stove Society Class Voucher		Test	Test		Test
4	1207748	Test1	05/29/2021 22:14:25 -07:00	########	22:14:25.7789511	Test1	250	0	0	0	0	0	250	0	250	Visa		Hot Stove Off Campus - Grilling with Tom	EventVouchers	1	125	125	0	0	0	125	06/24/2021 18:00:00 -07:00								Test1	Test1		Test1	Test1	Test1	Test1	Test1	Hot Stove Society Class Voucher		Test1	Test1		Test1
5	1207748	Test1	05/29/2021 22:14:25 -07:00	########	22:14:25.7789511	Test1	250	0	0	0	0	0	250	0	250	Visa		Hot Stove Off Campus - Grilling with Tom	EventVouchers	1	125	125	0	0	0	125	06/24/2021 18:00:00 -07:00								Test1	Test1		Test1	Test1	Test1	Test1	Test1	Hot Stove Society Class Voucher		Test1	Test1		Test1
6	1207719	Test2	05/29/2021 18:15:35 -07:00	########	18:15:35.7274103	Test2	170	0	0	0	0	0	170	0	170	Discover		Hot Stove Off Campus - Southeast Asian Grilling	EventVouchers	1	85	85	0	0	0	85	06/19/2021 18:00:00 -07:00								Test2	Test2	Test2	Test2	Test2	Test2	Test2	Test2	Hot Stove Society Class Voucher		Test2	Test2		Test2
7	1207719	Test2	05/29/2021 18:15:35 -07:00	########	18:15:35.7274103	Test2	170	0	0	0	0	0	170	0	170	Discover		Hot Stove Off Campus - Southeast Asian Grilling	EventVouchers	1	85	85	0	0	0	85	06/19/2021 18:00:00 -07:00								Test2	Test2	Test2	Test2	Test2	Test2	Test2	Test2	Hot Stove Society Class Voucher		Test2	Test2		Test2
8	1207653	Test3	05/29/2021 15:44:28 -07:00	########	15:44:28.7669810	Test3	90	0	0	0	0	0	90	85	5	MasterCard	Test3	Handmade Spring Pasta Virtual Class	EventVouchers	1	25	25	0	0	0	25	06/09/2021 18:00:00 -07:00								Test3	Test3		Test3	Test3	Test3	Test3	Test3	Hot Stove Society Class Voucher		Test3	Test3		Test3
9	1207653	Test3	05/29/2021 15:44:28 -07:00	########	15:44:28.7669810	Test3	90	0	0	0	0	0	90	85	5	MasterCard	Test3	Potsticker Love Virtual Class	EventVouchers	1	35	35	0	0	0	35	06/26/2021 10:00:00 -07:00								Test3	Test3		Test3	Test3	Test3	Test3	Test3	Hot Stove Society Class Voucher		Test3	Test3		Test3
10	1207653	Test3	05/29/2021 15:44:28 -07:00	########	15:44:28.7669810	Test3	90	0	0	0	0	0	90	85	5	MasterCard	Test3	Spring Gnocchi | Virtual Class	EventVouchers	1	30	30	0	0	0	30	06/18/2021 18:00:00 -07:00								Test3	Test3		Test3	Test3	Test3	Test3	Test3	Hot Stove Society Class Voucher		Test3	Test3		Test3
11	1185617	Test4	05/01/2021 08:16:56 -07:00	########	08:16:56.2255588	Test4	135	0	0	0	0	0	135	0	135	Visa		Cinco De Mayo Happy Hour Class + Ingredient Kit	EventVouchers	1	65	65	0	0	0	65	05/05/2021 16:30:00 -07:00								Test4	Test4		Test4	Test4	Test4	Test4	Test4	Hot Stove Society Class Voucher		Test4	Test4		Test4
12	1185617	Test4	05/01/2021 08:16:56 -07:00	########	08:16:56.2255588	Test4	135	0	0	0	0	0	135	0	135	Visa		Quick and Easy Halibut Dinner Class + Ingredient Kit	EventVouchers	1	70	70	0	0	0	70	05/11/2021 18:00:00 -07:00								Test4	Test4		Test4	Test4	Test4	Test4	Test4	Hot Stove Society Class Voucher		Test4	Test4		Test4
ItemsSold_5-1-2021_to_5-3

 

[mrshl9898]

So remove duplicate values in O if columns A D O and U are identical?

 

[muhleebbin]

I suppose it would be remove duplicate values in O if columns A and D are identical.

In the case of Test 3 and 4 U would be different but I'd still want O to be removed.

 

[mrshl9898]

Assuming the sorting stays as is. Perhaps (untested)

Sub doit() 

Dim rownum as long
Dim myorder as string
Dim mydate as string

Rownum = 2

Do until cells(rownum, 1) = "" 
myorder = cells(rownum, 1)
mydate = cells(rownum, 4)
rownum = rownum + 1
Do until cells(rownum, 1) <> myorder and cells(rownum, 4) <> mydate
Cells(rownum, 25).clearcontents
rownum = rownum + 1
Loop
Loop

End sub

 

[muhleebbin]

Sorry I got sidetracked on other projects but I did just try this out and got the following results

Original data before macro

ItemsSold_6-14-2021_to_6-24-2021 (1).xls
	A	B	C	D											O
1	Order Id	Reference Id	DATE	Date Only											Amount Paid
2	1229104	56EB2-W857	6/24/2021 2:38:22 PM -07:00	6/24/2021 12:00:00 AM											160
3	1229104	56EB2-W857	6/24/2021 2:38:22 PM -07:00	6/24/2021 12:00:00 AM											160
4	1229093	PZXV3-QFEH	6/24/2021 2:15:25 PM -07:00	6/24/2021 12:00:00 AM											80
5	1229083	CHWJA-ZHDQ	6/24/2021 2:07:01 PM -07:00	6/24/2021 12:00:00 AM											170
6	1229083	CHWJA-ZHDQ	6/24/2021 2:07:01 PM -07:00	6/24/2021 12:00:00 AM											170
7	1228631	CKG5F-UUY6	6/24/2021 1:37:48 AM -07:00	6/24/2021 12:00:00 AM											160
8	1228631	CKG5F-UUY6	6/24/2021 1:37:48 AM -07:00	6/24/2021 12:00:00 AM											160
9	1228446	ZENPA-XE7E	6/23/2021 3:27:07 PM -07:00	6/23/2021 12:00:00 AM											0
10	1228445	9TWW4-CRQU	6/23/2021 3:26:40 PM -07:00	6/23/2021 12:00:00 AM											0
11	1228437	MQWQZ-YF5F	6/23/2021 3:12:13 PM -07:00	6/23/2021 12:00:00 AM											250
12	1228437	MQWQZ-YF5F	6/23/2021 3:12:13 PM -07:00	6/23/2021 12:00:00 AM											250
ItemsSold_6-14-2021_to_6-

and after macro:

ItemsSold_6-14-2021_to_6-24-2021 (2).xlsx
	A	B	C	D											O
1	Order Id	Reference Id	DATE	Date Only											Amount Paid
2	1229104	56EB2-W857	06/24/2021 14:38:22 -07:00	########											160
3	1229104	56EB2-W857	06/24/2021 14:38:22 -07:00	########
4	1229093	PZXV3-QFEH	06/24/2021 14:15:25 -07:00	########
5	1229083	CHWJA-ZHDQ	06/24/2021 14:07:01 -07:00	########
6	1229083	CHWJA-ZHDQ	06/24/2021 14:07:01 -07:00	########
7	1228631	CKG5F-UUY6	06/24/2021 01:37:48 -07:00	########
8	1228631	CKG5F-UUY6	06/24/2021 01:37:48 -07:00	########
9	1228446	ZENPA-XE7E	06/23/2021 15:27:07 -07:00	########											0
10	1228445	9TWW4-CRQU	06/23/2021 15:26:40 -07:00	########
11	1228437	MQWQZ-YF5F	06/23/2021 15:12:13 -07:00	########
12	1228437	MQWQZ-YF5F	06/23/2021 15:12:13 -07:00	########
ItemsSold_6-14-2021_to_6-

I'm not entirely sure what happened to the values of 80, 170 and 250 and why those were cleared. I also did tweak the macro to "Cells(rownum, 15).clearcontents" as leaving 25 for the column cleared contents in Column Y Item Discount which didn't have any info really:

Sub doit()

Dim rownum as long
Dim myorder as string
Dim mydate as string

Rownum = 2

Do until cells(rownum, 1) = ""
myorder = cells(rownum, 1)
mydate = cells(rownum, 4)
rownum = rownum + 1
Do until cells(rownum, 1) <> myorder and cells(rownum, 4) <> mydate
Cells(rownum, 15).clearcontents
rownum = rownum + 1
Loop
Loop

End sub

 

[mrshl9898]

The problem is there are unique orders, unlike your example. Probably a bit short sighted of me but in future a more thorough representation of the data would be helpful ☺

I'll follow up when back at a PC in a few days if no one else gets the solution through first.