Summary Sheet For Identified Dupes

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

Mhz

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

Now that I have a way to ID duplicate Phone numbers accross multipl
sheets, I want only the name and number of these dupes applied to
summary or dupe sheet... I am flaging the dupes with counts, I need
formula to find any counts (Specifying a Duplicate), and in return prin
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
 
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
 
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(),""))
Click to expand...
(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

---
 
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...:rolleyes:
 
.. 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 ]

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

Don't think there's a function called: Direct <g>

---
 
Back
Top