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
),"",INDEX(B:B,MATCH(SMALL(D
,ROW(A1)),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
