Bruce, some thoughts here:
Surely each row has an invoice number? Are you looking for instances where
the same invoice might have been posted twice in error, or where two invoices
(with different numbers) may have been erroneously raised for the same
transaction?
I can't help with VB to automatically find and delete duplicates, but
assuming either will not be too numerous an occurrence, will
semi-automatically highlighting duplicates for manual deletion be sufficient?
It may even be the safest way, enabling you to visually check before deleting.
If so, let's look at invoices duplicated (with same number) in error: I
would record a macro that sorted by the invoice number column in ascending
order, then duplicates can easily be detected by a very simple formula.
Let's say for example, your invoice number is in column B beteen your
Account number and Date. Now out in, say, column G you can put in a formula
to test for duplicate invoice numbers as follows (assuming you are on row 3):
=IF(B3=B2,1,0) (but see "Note" below re deleting rows)
Now if you sum column G, you will have a total of the number of duplicates
to be deleted. The formula Sum(G:G) will sum the whole column for you, and
you can have that total reflected in a fixed, easy-to-see place. At (say)
the top of your worksheet you can reserve an area for warning messages, which
might be row 1. In (say) A1 enter the formula
=IF(SUM(G:G)>0,CONCATENATE("Warning: Duplicates = ",SUM(G:G)),"No
Duplicates"). That looks complicated but its not. The "Concatenate" function
merely joins the contents of two cells together, in this case your chosen
text plus the numerical value from G1000.
Given this information you can work down the sheet deleting duplicates until
the message "No Duplicates" appears at A1. Easier if in your macro you freeze
the windows to keep row A (warning row) visible.
Of course, you can record a second macro to re-sort into you regular order
and reverse any other actions performed by the first macro (such as
unprotecting the worksheet, and you may want to hide column G and row A,
because "No Duplicates" could be misleading at other times when not sorted
into invoice order
).
Note: If you use this formula in column G that compares the current cell to
the one above, deleting a row will corrupt the formula below as it will refer
to a non-existent cell. Thats a weakness of this method. Instead of deleting
a row, for ease, record a macro that deletes the contents of all non-formula
holding cells in the row, and when re-sorted it will just become a blank row
ready to be re-used. (Remember tho, that if you add columns later the macro
won't know about it and will delete the wrong cells, so to be smart you can
add hidden columns now, before recording the macro, for expanding the
spreadsheet later. Gosh...I wish I'd have done that! LOL)
If you want to use conditional formatting, you can make a cell background
turn red for example, if its contents (or the contents of another cell) are
1. Makes it even easier to find those duplicates!
Thats enough to think on for one post. If the problem wasn't duplicate
invoices (2 of the same number) but transactions raised twice on 2 different
invoices, we can talk about it again if you'd like.
Here's a final tip I learned (the hard, embarrassing way!) when I was a
beginner: If you sum columns or name ranges, always include a shaded row or
column at top and bottom of the range, so that if you add a row or column it
won't inadvertently get added outside a named range of summed column or row!
That can cost dearly in both dollars and embarrassement!
Cheers,
Kevryl
