Delete one row if data in two cells match

[jski21]

Good day Mr. Excel team,

Working through a rather large dataset and could alleviate some pain but need this groups assistance. The dataset represents transportation activity. One row for pickup, one row for return. I'd like to maintain the existing dataset for reference. If I could winnow done the data by eliminating one row of a matched set that would make the analysis much more manageable. Example:

STC Data.xlsx
	A	B	C
1	Record#	Client Name	Pickup Date
2	1	Customer 1	1/2/2017
3	2	Customer 1	1/2/2017
4	3	Customer 2	1/2/2017
5	4	Customer 2	1/2/2017
6	5	Customer 3	1/3/2017
7	6	Customer 3	1/3/2017
STC 2017-2022

If the Client Name in Column B and the Pickup Date in Column C on Row 1 and Row 2 are the same (this is a round trip), Row 2 would be deleted. On to Record #3. Same exercise. On to Record #5, etc. The dataset is fairly large (out to Column BJ) and lengthy (Row 578285).

I could simply run the analysis and divide by two; however, some of these are one-ways and I need to be on target with the analysis. VBA seems to be the best direction, but need a bit of direction or other ideas.

Thanks in advance for the review and counsel.

Best regards,

jski

 

[igold]

Does this work for you. I am not sure on the speed with 500,000 rows... Make sure to test on a backup copy of your data.

Sub RemDupes()
    Cells.RemoveDuplicates Columns:=Array(2, 3), Header:=xlYes
End Sub

 

[jski21]

Hmmm...no. I'm getting a 1004; Application-defined or object-defined error.

 

[igold]

ehhhhhhh....... worked for me on a few less rows... Perhaps RemoveDuplicates is not included with Excel 2016.

Nonetheless, I was happy to try and help.

 

[jski21]

Np. Appreciate the time and consideration igold. Thanks.

 

[jski21]

Good day gents,

I think I figured this out with the following code:

---------------------------------------------------------------------------------------------------
Sub RemDupes()

'Declared variables
Dim dataRange As Range


'Change Range cells as necessary to cover entire dataRange
Set dataRange = Range("A1:BJ578285")

'Change array numbers to the columns in which you are looking for duplicates
dataRange.RemoveDuplicates Columns:=Array(2, 3), Header:=xlYes

End Sub
---------------------------------------------------------------------------------------------------


I mentioned previously that my dataset was related to transportation (round trips) so there would be dupes. The dataset was 578,285 rows. After running the code, that was reduced to 298,651 which is a little over half if dividing the dataset by 2. So there are about 9,500 one-way trips in the dataset over a 6-year period which seems ok from a reasonable ness standpoint. I'll take it.

BTW, my Excel version is 2016.


Posting the code here for future users who may come across the same headache/need as I did.

Regards,

jski