Remove Duplicates From Worksheet

[jo15765]

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

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]

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]

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]

This thread should probably be in Excel, not Access.

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

 

[arnelgp]

you don't need code/vba, excel has that feature (Data->Remove Duplicates)

 

[Isaac]

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]

Use conditional formatting to show the dupes prior to deleting.

 

[arnelgp]

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]

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]

no it's not, there is No Macro Involved in my solution.

 

[Isaac]

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.