Using EXACT to Deduplicate

  • Thread starter Thread starter Darrell Halverson via OfficeKB.com
  • Start date Start date
D

Darrell Halverson via OfficeKB.com

I want to use the EXACT function to deduplicate multiple real estate ads in
Column I (compare the target cell to every other cell in Column I) of a
very large spreadsheet. Once a duplicate is found, I want the macro to
look in column B and compare dates, deleting all data in the row with the
more recent date, preserving the deduplicated ad in situ in the
spreadsheet. This would allow me to know/save the first appearance of an
ad and not save its later repetitions. Any help appreciated. Thank you.
 
Thank you for the suggestion. I downloaded ASAP Utilities and it is,
indeed, loaded with interesting and useful functionalities to leverage
Excel. Unfortunately, I found that it has only a "count duplicates"
function wherein it identifies duplicates, paints them green and then,
chokes on my typical dedupe cell which has 100-200+ characters in many
instances.

So I am back to looking for some help with the EXACT function to do this
job.
 
Highlight the column, invoke ASAPUtilities > "Columns/Rows" > "Conditional
Row and column, select, hide or delete" > then in the Search Options: choose
"duplicate values" > and check the "Delete" button..........

Vaya con Dios,
Chuck, CABGx3
 
Thank you for your more detailed suggestion. It works with simple alpha or
numeric strings, but continues to choke on the more complex alphanumeric
strings of a typical 100-200 character real estate ad. I get the following
Windows error message: "Run-time error '1004': Unable to get the CountIf
property of the WorksheetFunction class."

It's also a little buggy in that it appropriates an adjacent column now and
then, but rejects the user selecting two columns to dedupe. My
spreadsheets are moderately formatted and maybe it doesn't like that
either. In any event, when it deletes, it saves
"the first duplicate" not necessarily the earliest (date) duplicate which
is what I need.

Thanks again for you help. I'm going to study up on the EXACT function to
see what can be done with that.
 
Back
Top