Solved

Ms Excel

Posted on 2011-03-05
13
347 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 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

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

856 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