Results 1 to 3 of 3

Thread: How to find total number of duplicates from lists of sheet in excel

  1. #1
    Join Date
    Dec 2011
    Posts
    69

    How to find total number of duplicates from lists of sheet in excel

    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. That’s why I need to have a formula to check across 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.

  2. #2
    Join Date
    Aug 2011
    Posts
    566

    Re: How to find total number of duplicate from lists of sheet in excel

    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. 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),"",INDEX(B:B,MATCH(SMALL(D: D,ROW(A1)),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

  3. #3
    Join Date
    Aug 2011
    Posts
    695

    Re: How to find total number of duplicate from lists of sheet in excel

    According to my information you can make the use of the application, or the program that are available on the internet. There are several of the application are available for this thing to make happen in Excel. So you can make the use of Duplicate Master, 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. This is one of the simple tricks to make this thing happen in excel.

Similar Threads

  1. Replies: 2
    Last Post: 23-02-2012, 02:59 PM
  2. Replies: 2
    Last Post: 17-02-2012, 04:13 PM
  3. Formula for serial number in Excel Sheet
    By Aarti C in forum Microsoft Project
    Replies: 2
    Last Post: 08-01-2012, 08:23 PM
  4. Replies: 3
    Last Post: 01-06-2010, 04:31 AM
  5. How to find first occurance of a number in Excel
    By AZUL in forum Windows Software
    Replies: 3
    Last Post: 11-06-2009, 10:32 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,696,430.18824 seconds with 17 queries