counting duplicates Among Many Sheets, Possible??

[Mhz]

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..

[Franz Verga]

Mhz wrote:
>> 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..


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

http://www.cpearson.com/excel/duplic...tingDuplicates

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Max]

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
> 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
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=557662
>
>

[Mhz]

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

[m96]

Try Duplicate Master:
http://members.iinet.net.au/~brettdj/

As Application scope you can either choose Entire workbook or choose Range
and click on the different sheets or sheets/columns which you want to search.

Works great!

BR,

[Max]

You're welcome, Mhz !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
> 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