Quick Dupe Check

[jomili]

I have a report that is 14K rows deep, 428 columns wide. I need to find (not delete) all duplicates in a single column ("Posn").

If I do Conditional Formatting to highlight the dupes, the CF appears to work quickly, but when I go to filter on those results (to filter to my dupes), my filter takes five minutes.

Is there a faster way?

[TMS]

In the old days, I would have added a Helper column and used a COUNTIF to identify rows with duplicated entries. Then you can filter on the Helper column.

You can use a simple COUNTIF, for example:

Please Login or Register  to view this content.

or, if you are interested in omitting the first of the entries, you can use:

Please Login or Register  to view this content.

Conditional formatting can be slow and a bit of a pain over large ranges of cells

[jomili]

TMS, I'm so sorry I didn't reply sooner. I never got notified that anyone had replied to this thread, so this is the first day I've come back to it. The countif method works, I just have to add and then delete a column. I was hoping that someone had developed a really quick way to do this.

[jomili]

Okay, I have a solution, but I'm not entirely happy with it. It's marginally faster than Conditional Formatting, but with 15,000 rows (only one column) it still takes a long time. if anybody can suggest a way to speed it up I'd love to hear it.

Please Login or Register  to view this content.

[jomili]

Okay, two possibilities, both as slow as molasses on my range (single column, 15000 rows of almost all unique values). This is a nasty sheet so I have to determine the RealUsedRange. I still need help on this.

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[jomili]

Holy Smokes, that was fast! Much better than the approach I was taking. I changed it a bit to make it variable to any column I want it to work on. At some point I'd like to make it more Row variable (in case my range starts at, say, F25), but what you've given me is way above what I've been working with. Thanks so much!

Please Login or Register  to view this content.

[Fluff13]

Glad to help & thanks for the feedback.

If you are ever going to be using column AA onwards, this would be a better way of getting the column letter

Please Login or Register  to view this content.

[jomili]

Very useful. Thanks.

[Fluff13]

You're welcome