Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
Extending the earlier set-up [ 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
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 --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified 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... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561041 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
.. 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"),MAT CH(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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
...Commands (Indirect,Direct,Lookup,etc..)
Don't think there's a function called: Direct <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Summary Sheet For Identified Dupes
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup info in support sheets and enter in summary sheet | Excel Discussion (Misc queries) | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
How do I reference values from 200 worksheets onto a summary sheet | Excel Discussion (Misc queries) |