Finding duplicate records

  • Thread starter Thread starter Mortar Man
  • Start date Start date
M

Mortar Man

Hi all. Some help please.
Every month I inherit a spreadsheet I have to work with. It is
compiled by people who are not very computer literate and at times
contains many errors. I am required to find these errors and send
the repaired spreadsheet onto someone else.
The spreadsheet is a simple spreadsheet using colums A through to
W. What I need to do is to find the duplicate records, identify which
of those duplicate records contains a value of $60.00 in a certain
column then paste those duplicate records with that value into a new
worksheet.
The relevant information is:

1. Duplicate records can be identified by a eight digit Invoice
Number which is Column D,
2. The column which would contain the $60.00 value is Column I,
3. Column D does contain blank fields which should be ignored,
4. Rows 1 - 7 are headings which should be ignored, and
5. The new worksheet should be added to the current document.

Can the above be achieved with a macro? What is the best way of
solving this problem? Any ideas appreciated.

Thanks in advance.

PD
 
=SUM(1/COUNTIF(D8:D100,D8:D100))
Is the number of unique values in D8:D100. This should equal
=COUNTA(D8:D100)+SIGN(COUNTBLANK(D8:D100))
if there are no duplicates

In row 8 of a parallel column,
=IF(ISBLANK(D8),"",IF(COUNTIF(D$8:D$100,D8)>1,"Duplicated",""))
will tell you if the invoice number in D8 is duplicated elsewhere. Copy
the formula down to ID all duplicates

You could proceed similarly to identify $60.00, or use conditional
formatting.

Jerry
 
Back
Top