Solved

Ms Excel

Posted on 2011-03-05
13
346 Views
Last Modified: 2012-05-11
i am having two excels from the old excels i want to check duplicate entries in new excel means i want to check double entries of some data if i will check one by one it will take time please suggest.
0
Comment
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35042098
So what defines a duplicate?

Here's a recent tip I did where the OP wanted to delete any duplicates in Column A.  Easy to modify for another column.

Does this help?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35042100
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35042107
Hello,

Here are a few commas and full stops

,,,,

...

Please put them into your question, so that it can be understood when we read it.

cheers, teylyn
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 41

Expert Comment

by:dlmille
ID: 35042115
Also, if you have Excel 2007+ you can select the column where you want to check for duplicates, then from the Ribbon, you can delete duplicates.

See picture:
 duplicate removalDave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35042121
Yogesh - my picture above didn't have enough pointers to it - the one in Excel 2007 is on the Data Ribbon, under Data Tools - See revised picture.
updatedAgain, further tips and VBA code for deleting duplicates can be found here:

Here's the post:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26750772.html


Cheers,

Dave
0
 

Author Comment

by:Yogesh_Exchange_Expert
ID: 35042175
actually the thing is that
i have 2 excel sheets E1 and E2 both are dissimilar in some entries
E2 have 400 entries
E1 have 300 entries
so there is a column with a name "Host name" i just want to check which host names are present in E2 but not present in E1.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35042180
so there is a column with a name "Host name" i just want to check which host names are present in E2 but not present in E1.

>>> Use a simple Vlookup Formula to achieve that.

Sid
0
 

Author Comment

by:Yogesh_Exchange_Expert
ID: 35042199
Vlookup means finding entries one by one or any thing else.
in the hostnames column there are different names of servers present. please help me out how could i find names are not in E1 but they are into E2.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35042201
Sample coming up...

Sid
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35042203
Yogesh - apologies for the link (blushing) :)  It was probably not that useful and can't seem to put my hands on a good one....

The above tip with the graphic is a good one.

Here are some approaches to also explore/persue:

1.  you can insert a column - say insert column A (where you're checking for column B).  Then put the formula (starting say at A2
    [A2] =CountIf(B:B,B2) and copy it all the way down.  This will deliver a number from 1 to many.  Anything > 1 is a duplicate.  You can then create a
    data filter on Column B to see where all your duplicates are.  You can select and delete any duplicates you see, leaving one row behind.

2.  you can also use this macro - place it in your VBA project module:  It can be found at Chip Pearson's website:
http://www.cpearson.com/excel/deleting.htm

and the code is below - just copy/paste into a module in your workbook.


But, if you have Excel 2007+ using the above method (with the picture) is a good bet.  If you're trying to AUTOMATE and delete duplicate rows on multiple sheets, you can do the following.  Use the Chip Pearson code in your workbook (Item #2, above), then use the below code...
Public Sub deleteDupsAllSheets()
Dim mySheet As Worksheet
Dim myStartSheet As Worksheet

    Set myStartSheet = ActiveSheet
    For Each mySheet In Application.Worksheets
        mySheet.Activate
        Range("A1").Select
        Call DeleteDuplicateRows
    Next mySheet
    
    myStartSheet.Activate
End Sub

Open in new window


See attached (which includes the DeleteDuplicateRows() routine from C Pearson's website).  And just change the Range reference to your column of choice for searching for duplicats...
Cheers,

Dave

Cheers,

Dave
DeleteDuplicateRows-ColA-r1.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35042209
I see Dave has already given you an example so I will now retreat gracefully :)

Sid
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35042261
Yogesh.  If you have two sheets, with columns that you are comparing you can take this approach.

The formula =IF(ISNA(MATCH(A2,'E2'!A:A,0)),0,1)  in cell A2 (copied down) will show you a "1" with all those that MATCH in E2 sheet.

And in E2, the formula =IF(ISNA(MATCH(A2,'E1'!A:A,0)),0,1) in cell A2 (copied down) will show you a "1" with all those that MATCH in E1 sheet.

So, if you use a Data Filter, you can filter the "1"'s to find those that are duplicated in both sheets.

Is this helpful?

See attached (I had some made up data for Column A in the E1 and E2 Sheets.

Cheers,

Dave
Compare-two-columns-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35042273
Yogesh - if you would supply a sample (small is ok) non-confidential file, I could customize this for you, if the last post gives you any difficulties.

Cheers,

Dave
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When the confidentiality and security of your data is a must, trust the highly encrypted cloud fax portfolio used by 12 million businesses worldwide, including nearly half of the Fortune 500.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Sometimes we receive PDF files that are in the wrong orientation. They may be sideways or even upside down. This most commonly happens with scanned or faxed documents. It is possible to rotate the view of these PDFs with the free Adobe Reader produc…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question