counting duplicates Among Many Sheets, Possible??

  • Thread starter Thread starter Mhz
  • Start date Start date
M

Mhz

:confused: Hi, I have spent countless days trying to find a formula that
will allow me to find duplicates across Multiple Sheets within a single
workbook. All resources I have checked only allow duplicate finding
formulas for A single Sheet.

I have multiple sheets 1 to 31 representing a month in which I have a
column of phone numbers. i want to be able to know from sheet to sheet
(Day to Day) if I may be retyping a number from a previous day. Thats
why I need to have a formula to check acrosss the sheets for duplicate
numbers...

even better if I can have the duplicates displayed on a new sheet...

Any formula or help would be much appreciated..Thanks In Advance..
 
Mhz said:
will allow me to find duplicates across Multiple Sheets within a
single workbook. All resources I have checked only allow duplicate
finding formulas for A single Sheet.

I have multiple sheets 1 to 31 representing a month in which I have a
column of phone numbers. i want to be able to know from sheet to
sheet (Day to Day) if I may be retyping a number from a previous day.
Thats why I need to have a formula to check acrosss the sheets for
duplicate numbers...

even better if I can have the duplicates displayed on a new sheet...

Any formula or help would be much appreciated..Thanks In Advance..
Click to expand...


Maybe you could find usefule this page at Chip Pearson's site:

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Here's an approach using non-array formulas which dynamically gathers & lists
all tel #s from all source sheets (identically structured) into a single col
in a summary sheet, then flags duplicate tel#s (if any) and extracts a
"master" list of unique tel#s for ref.

A sample construct is available at:
http://www.savefile.com/files/5448014
Dynamic data list fr 31 shts n Flag dups n Extract uniques.xls

Assume tel #s would be listed within A1:A10* in 3 source sheets named simply
as: 1, 2, 3. *max expected data extent is say: 10 rows per sheet

In a new sheet: Summary (say),

Col headers placed in A1:C1, and in E1
In A1: In sheet
In B1: Tel# List
In C1: Dup Tel#?
In E1: List of unique Tel# (from all source sheets)

In A2:
=INT((ROW(A1)-1)/10)+1

In B2:
=OFFSET(INDIRECT("'"&INT((ROW(A1)-1)/10)+1&"'!A1"),MOD(ROW(A1)-1,10),)

Note: Just change the "10" in the formulas in A2 and B2 to a figure equal to
the max expected number of rows of source data

In C2:
=IF(B2=0,"",IF(COUNTIF($B$2:B2,B2)>1,"Dup",""))

In D2:
=IF(B2=0,"",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))
(Leave D1 empty)

In E2:
=IF(ROW(A1)>COUNT(D:D),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

Select A2:E2, fill down to E31, to cover the max expected aggregated extent
of source data. In this example, the max is 10 rows per sheet x 3 sheets = 30
rows total. (Extend the formulas fill to suit your actual aggregate)

Cols A auto-labels sequentially the sheetnames: 1, 2, 3 (repeating
automatically each sheetname for 10 rows) while col B lists the corresponding
tel# entries within A1:A10 from each sheet. Zeros will be returned in col B
for any empty source cells.

Col C will flag duplicate tel #s within col B, if any, for reference ("Dup").
Just autofilter on C1 as needed. To count the # of duplicates, just use in
any cell (other than within col C): =COUNTIF(C:C,"Dup")

Col D is a criteria col for col E to dynamically extract a uniques list of
tel #s from col B (Col D can be hidden away)

Col E extracts the List of unique Tel# for reference

---
 
Thanks for the replies... Quite interesting Max and very detailed thanks
alot.. I have multiple columns with names, phone remarks, etc on each
sheet, so I think the master Page will be more ideal for capturing the
dupes. Some of the programing you wrote is a bit over my head at the
time, but I will study it... I am still in an old world of BASIC
programming and havn't quite had the time to get up to par on Visual
Basic. But thanks anyhow, I'll see what I can put together on the
wonderful info you have given me... Thanks Much :)
 
Back
Top