Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]() ![]() will allow me to find duplicates across Multiple Sheets within a single workbook. All resources I have checked only allow duplicate finding formulas for A single Sheet. I have multiple sheets 1 to 31 representing a month in which I have a column of phone numbers. i want to be able to know from sheet to sheet (Day to Day) if I may be retyping a number from a previous day. Thats why I need to have a formula to check acrosss the sheets for duplicate numbers... even better if I can have the duplicates displayed on a new sheet... Any formula or help would be much appreciated..Thanks In Advance.. -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=557662 |
#2
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Mhz wrote:
confused: Hi, I have spent countless days trying to find a formula that will allow me to find duplicates across Multiple Sheets within a single workbook. All resources I have checked only allow duplicate finding formulas for A single Sheet. I have multiple sheets 1 to 31 representing a month in which I have a column of phone numbers. i want to be able to know from sheet to sheet (Day to Day) if I may be retyping a number from a previous day. Thats why I need to have a formula to check acrosss the sheets for duplicate numbers... even better if I can have the duplicates displayed on a new sheet... Any formula or help would be much appreciated..Thanks In Advance.. Maybe you could find usefule this page at Chip Pearson's site: http://www.cpearson.com/excel/duplic...tingDuplicates -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
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:D),"",INDEX(B:B,MATCH(SMALL(D: D,ROW(A1)),D: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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: ![]() will allow me to find duplicates across Multiple Sheets within a single workbook. All resources I have checked only allow duplicate finding formulas for A single Sheet. I have multiple sheets 1 to 31 representing a month in which I have a column of phone numbers. i want to be able to know from sheet to sheet (Day to Day) if I may be retyping a number from a previous day. Thats why I need to have a formula to check acrosss the sheets for duplicate numbers... even better if I can have the duplicates displayed on a new sheet... Any formula or help would be much appreciated..Thanks In Advance.. -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=557662 |
#4
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]() Thanks for the replies... Quite interesting Max and very detailed thanks alot.. I have multiple columns with names, phone remarks, etc on each sheet, so I think the master Page will be more ideal for capturing the dupes. Some of the programing you wrote is a bit over my head at the time, but I will study it... I am still in an old world of BASIC programming and havn't quite had the time to get up to par on Visual Basic. But thanks anyhow, I'll see what I can put together on the wonderful info you have given me... Thanks Much :) -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=557662 |
#5
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Try Duplicate Master:
http://members.iinet.net.au/~brettdj/ As Application scope you can either choose Entire workbook or choose Range and click on the different sheets or sheets/columns which you want to search. Works great! BR, |
#6
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
You're welcome, Mhz !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: Thanks for the replies... Quite interesting Max and very detailed thanks alot.. I have multiple columns with names, phone remarks, etc on each sheet, so I think the master Page will be more ideal for capturing the dupes. Some of the programing you wrote is a bit over my head at the time, but I will study it... I am still in an old world of BASIC programming and havn't quite had the time to get up to par on Visual Basic. But thanks anyhow, I'll see what I can put together on the wonderful info you have given me... Thanks Much :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Counting occurrences over range of sheets | Excel Worksheet Functions | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Counting text across multiple sheets with a specific criterion | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |