Solved

Ms Excel

Posted on 2011-03-05
13
343 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

PaperPort 14.5 Patch 1 update is often not detected or downloaded automatically. This article provides direct download links to solve the problem for retail (non-bundled) versions of the Standard and Professional editions, as well as the Professiona…
Online collaboration can help businesses be more efficient, help employees grow their skills and foster a team environment.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now