Summary Sheet For Identified Dupes

[Mhz]

I not long ago had Great Info Provided By Max similar to what I'm requesting here, but hopefully a bit simpler.

Now that I have a way to ID duplicate Phone numbers accross multiple sheets, I want only the name and number of these dupes applied to a summary or dupe sheet... I am flaging the dupes with counts, I need a formula to find any counts (Specifying a Duplicate), and in return print the Name and Phone of that dupe on a Summary or Duplicate sheet.

e.g. If column A6:A35 is Greater than 0 then Print the Name (Column B) and Phone (Column C) to Summary or Dupe Sheet.

I'm assuming these formulas would be on the Summary or Dupe Sheet to Identify the Multiple sheets for Dupes, Not sure.

Thanks In Advance for any help here.

[Max]

Extending the earlier set-up [ re: http://tinyurl.com/zo9nf ]
here's a non-array formulas play which delivers what you're after ..

Extended sample is available at:
http://www.savefile.com/files/5242623
Dyn data listing fr 31shts n Extr dupes n uniques.xls

Assume the names are listed in col B in each of the 31 daily sheets
(col A = tel#s)

In Summary,

Labels placed in G1:I1 : Dupes In sheet, Tel#, Name

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

In G2:
=IF(ROW(A1)>COUNT($F:$F),"",
INDEX(A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy G2 to H2

In I2:
=IF(G2="","",
INDEX(INDIRECT("'"&G2&"'!B:B"),MATCH(H2,INDIRECT("'"&G2&"'!A:A"),0)))

Select F2:I2, fill down to I31
(cover the same extent as the earlier set-up in cols A to E)

Cols G to I will return the required results, all neatly bunched at the top

Col G = Dupes In sheet, will tell you the sheets that the dupes are in
Col H = Tel#, will list the duplicated tel#s
Col I = Name, will extract the corresponding names from the particular sheets
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
> I not long ago had Great Info Provided By Max similar to what I'm
> requesting here, but hopefully a bit simpler.
>
> Now that I have a way to ID duplicate Phone numbers accross multiple
> sheets, I want only the name and number of these dupes applied to a
> summary or dupe sheet... I am flaging the dupes with counts, I need a
> formula to find any counts (Specifying a Duplicate), and in return print
> the Name and Phone of that dupe on a Summary or Duplicate sheet.
>
> Thanks In Advance for any help here.
>
> e.g. If column A6:A35 is Greater than 1 then Print the Name (Column B)
> and Phone (Column C) to Summary or Dupe Sheet.
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=561041
>
>

[Max]

Think the formulas in col F could be simplified a little by having it read
the flags (arb. row#s) returned in col D*, the criteria col used picking off
uniques in the earlier set-up

*In D2, copied down, was:
=IF(B2=0,"",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))

So instead of
> In F2: =IF(B2=0,"",IF(COUNTIF($B$2:B2,B2)>1,ROW(),""))
(F2 is similar to D2, except for the swapped around values_if_TRUE/FALSE)

we could also use in F2, copied down:
=IF(B2=0,"",IF(D2="",ROW(),""))
as the criteria col to pick off the dupes
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Mhz]

Thanks Max, Once Again ...

I finally used the previous project you presented to me with a fresh file so I could sort of grasp how some of the formulas are behaving. I did this for the fact that my Telephone Column actually starts at (E6:E35), as well as Names (D6:D35), So I didn't quite know how to modify the complex formulas to fit my sheet ranges. I also had to deal with my 31 tabs named (DAY1...DAY31), puzzled me for a while so thats why I started from scratch to understand the formula actions.

I have sort of grasped some of the actions now, I had recently purchased the "F1 Formulas" PDF and it has helped me interpret some of those Monsterous Commands (Indirect,Direct,Lookup,etc..) quite decent Book.

Anyhow thanks alot for your time, I'll keep hammering away until I get my Program File Tweaked...

[Max]

> .. my Telephone Column actually starts at (E6:E35), as well as Names (D6:D35)

Here's a sample adapted to suit the above [assuming 3 daily sheets: 1,2,3]
http://www.savefile.com/files/1370467
Dyn data listing fr 31shts n Extr dupes n uniques_1.xls

In Summary,

In A2:
=INT((ROW(A1)-1)/30)+1
("10" changed to 30, since the range per sheet is now 30 rows, viz: E6:E35)

In B2:
=OFFSET(INDIRECT("'"&INT((ROW(A1)-1)/30)+1&"'!E6"),MOD(ROW(A1)-1,30),)
("10" changed to 30, since the range per sheet is 30 rows. The OFFSET
reference is also changed from "A1" to the new top cell for the tel#s, ie
cell E6, re the part: ... "'!E6" within the INDIRECT)

[Formulas below in C2:H2 remain unchanged, repeated for completeness]
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()))
In E2:
=IF(ROW(A1)>COUNT(D:D),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))
In F2:
=IF(B2=0,"",IF(D2="",ROW(),""))
In G2:
=IF(ROW(A1)>COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
G2 copied to H2
(Formulas in C2:H2 remain unchanged)

In I2:
=IF(G2="","",INDEX(INDIRECT("'"&G2&"'!D6:D35"),MATCH(H2,INDIRECT("'"&G2&"'!E6:E35"),0)))
(I2 is modified to point to the new ranges for tel# and names within each
sheet)

Select A2:I2, fill down to I91
(3 sheets x 30 rows per sheet = 90 rows to be filled now)

For the actual case, with a total of 31 daily sheets, fill down to I931
(31 sheets x 30 rows per sheet=931)

[ Just do a one-time effort to rename your daily sheets to pure numbers:
1,2,3,...31 ]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
>
> Thanks Max, Once Again ...
>
> I finally used the previous project you presented to me with a fresh
> file so I could sort of grasp how some of the formulas are behaving. I
> did this for the fact that my Telephone Column actually starts at
> (E6:E35), as well as Names (D6:D35), So I didn't quite know how to
> modify the complex formulas to fit my sheet ranges. I also had to
> deal with my 31 tabs named (DAY1...DAY31), puzzled me for a while so
> thats why I started from scratch to understand the formula actions.
>
> I have sort of grasped some of the actions now, I had recently
> purchased the "F1 Formulas" PDF and it has helped me interpret some of
> those Monsterous Commands (Indirect,Direct,Lookup,etc..) quite decent
> Book.
>
> Anyhow thanks alot for your time, I'll keep hammering away until I get
> my Program File Tweaked...
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=561041
>
>

[Max]

>> ...Commands (Indirect,Direct,Lookup,etc..)

Don't think there's a function called: Direct <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

Typo, line:
> (31 sheets x 30 rows per sheet=931)

should read as:
> (31 sheets x 30 rows per sheet = 930 rows)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---