?
Solved

Ms Excel

Posted on 2011-03-05
13
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 42

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 42

Expert Comment

by:dlmille
ID: 35042100
0
 
LVL 50
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

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 42

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 42

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 42

Accepted Solution

by:
dlmille earned 2000 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 42

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

719 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