Solved

Ms Excel

Posted on 2011-03-05
13
342 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:teylyn
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VLOOKUP to a Closed Workbook 22 65
VBA Code Mixed Combining Two User Forms 7 38
Excel 6 19
Delete rows if they are duplicates 3 15
I. Introduction In a previous article (http://www.experts-exchange.com/Web_Development/Document_Imaging/A_6537-PaperPort-Upgrade-How-to-download-and-install-updated-versions-of-PaperPort-11-and-12.html) (now deprecated), I discussed how to upgrad…
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 …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

759 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

20 Experts available now in Live!

Get 1:1 Help Now