Remove Duplicates From Worksheet (1 Viewer)

jo15765

Registered User.
Local time
Today, 09:01
Joined
Jun 24, 2011
Messages
130
I have an excel worksheet that in column A has an IP Address, and column B has a userid. If the user visits the same site multiple times, obviously there would be multiple entires in the worksheet for that.

I need a way to get rid of all duplicates. Sample data is like this

Code:
11111111 a12
22222222 a12
11111111 a12
33333333 a12
44444444 a12
11111111 a12

Of course the production data sheet has more than 6 rows, but this is for illustration purposes.
 

Kayleigh

Member
Local time
Today, 16:01
Joined
Sep 24, 2020
Messages
706
I think the simplest way to do this is to run a query to find the duplicate data and then run a delete query to remove it. See this link

Are you looking to do this in VBA?

Good luck,
Krayna
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
14,059
Sort by IP then UserID
Create a formula to count from the top to current row for those cells (CountIFS())
Then copy the formula to the bottom of the sheet.
The filter for count > 1 and delete those rows.

HTH
 

Isaac

Lifelong Learner
Local time
Today, 09:01
Joined
Mar 14, 2017
Messages
8,738
This thread should probably be in Excel, not Access.

Write code on the Excel object model to use Excel's RemoveDuplicates feature.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:01
Joined
May 7, 2009
Messages
19,175
you don't need code/vba, excel has that feature (Data->Remove Duplicates)
 

Isaac

Lifelong Learner
Local time
Today, 09:01
Joined
Mar 14, 2017
Messages
8,738
you don't need code/vba, excel has that feature (Data->Remove Duplicates)
That's the same as what I suggested. RemoveDuplicates method is Data > Remove Duplicates.
The question was posted in VBA forum.
 

AccessBlaster

Registered User.
Local time
Today, 09:01
Joined
May 22, 2010
Messages
5,830
Use conditional formatting to show the dupes prior to deleting.

1602560769602.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:01
Joined
May 7, 2009
Messages
19,175
That's the same as what I suggested. RemoveDuplicates method is Data > Remove Duplicates.
The question was posted in VBA forum.
See hte link, its different.
 

Isaac

Lifelong Learner
Local time
Today, 09:01
Joined
Mar 14, 2017
Messages
8,738
See hte link, its different.
No, it's the same.

You can prove it to yourself by recording a macro while using Data > Remove Duplicates manually. The resulting macro recorded code will use the suggested method.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:01
Joined
May 7, 2009
Messages
19,175
no it's not, there is No Macro Involved in my solution.
 

Isaac

Lifelong Learner
Local time
Today, 09:01
Joined
Mar 14, 2017
Messages
8,738
You're not understanding what I'm saying:
The VBA RemoveDuplicate methods IS the same as the Data > Remove duplicates.
You could prove that to yourself (if you wanted to), by recording a macro while using manual Data > Removeduplicates method, you will discover that the code produced does, indeed, use the RemoveDuplicates method.

At any rate, I would assume someone posting a solution in VBA forum wanted a automated solution, as that is kind of what we do here.
 

Users who are viewing this thread

Top Bottom