|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?action=getinfo&userid=35980
View this thread: http://www.excelforum.com/showthread.php?threadid=561041
|
|
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:
[Quoted Text] > 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?action=getinfo&userid=35980> View this thread: http://www.excelforum.com/showthread.php?threadid=561041> > |
|
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
[Quoted Text] > 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
---
|
|
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:
--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
View this thread: http://www.excelforum.com/showthread.php?threadid=561041
|
|
|
[Quoted Text] > .. 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...:rolleyes:
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=561041
>
>
|
|
|
|
Typo, line:
[Quoted Text] > (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
---
|
|
|