Duplicate entires in multiple tabs

  • Thread starter Thread starter Breezy
  • Start date Start date
B

Breezy

I need something to show me if I enter a duplicate field in a tab in a
worksheet. For example; I enter a list of invoice numbers on a tab "10.27.09"
in col B. Each day, I add a new tab with that days invoices numbers listed. I
want to know if I have entered an invoice number on tab 10.27.09 and again on
11.2.09. I just want something that will highlight the field. Is that
possible?
 
This is something that, if done with formulas, would need the formula rebuilt
each day. This solution assumes you only need to test the previous
day's/tab's contents for duplicates.

Pick any available empty column on the latest tab (11.2.09) and enter this
formula into the first row with invoices numbers entered. This formula
assumes that is on row 2 and that the invoice numbers are in column A on both
sheets:

=COUNTIF(10.27.09!A:A,A2)
fill the formula on down to the end of the list of invoice numbers on
11.2.09. A quick way to do that is to enter the formula, select that cell
again and move the mousepointer to the lower left corner of the cell until it
becomes a small + symbol (not the big cross it normally is, and not the
4-pointed cross that means to move the cell). When it turns into the +
symbol, double-click it and the formula will fill down the sheet adjacent to
all cells in the column next to it that have entries. See Excel help on
"FILL Data" for other ways to do the fill.

What will happen is that a 0 (zero) will be displayed if the invoice on
11.2.09 wasn't entered on 10.27.09, and a non-zero value such as 1 will be
displayed if the invoice entry on 11.2.09 duplicates one already on the
10.27.09 tab. You could filter the column with the formulas in it to show
just the duplicated ones: [Data --> Filter --> AutoFilter].
 
Back
Top