Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 08-01-2012
Member
 
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.

Reply With Quote
  #2  
Old 08-01-2012
Member
 
Join Date: Aug 2011
Posts: 542
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
Reply With Quote
  #3  
Old 08-01-2012
Member
 
Join Date: Aug 2011
Posts: 657
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.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to find total number of duplicates from lists of sheet in excel"
Thread Thread Starter Forum Replies Last Post
How to use Mail Merge from one Excel Sheet to another Excel Sheet Abélard MS Office Support 2 23-02-2012 01:59 PM
Where to find a Commercial Construction Cost Breakdown Estimate Sheet for Excel AsceTic! MS Office Support 2 17-02-2012 03:13 PM
Formula for serial number in Excel Sheet Aarti C Microsoft Project 2 08-01-2012 07:23 PM
how can i highlight cells with color, which contain duplicates form a separate excel sheet guigey Windows Software 3 01-06-2010 04:31 AM
How to find first occurance of a number in Excel AZUL Windows Software 3 11-06-2009 10:32 PM


All times are GMT +5.5. The time now is 11:23 PM.