Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Comparing data in two worksheets to find duplicates

Hi All,

I have attached a worksheet for your reference. I am a new starter in the world of data validation. My task includes to make sure that I only pay for jobs which are original and not invoiced to us in the past. In order to solve duplication issue, I want to compare two worsheets, sheet 1 has the jobs invoiced to us for the current month and sheet two has got standard report from the past 12 months giving all jobs invoiced to us in the past 12 months. I need a VBA based macro to find all matching jobs which are in sheet 1 column A, in sheet2 column A and return the corresponding value in column B in sheet 2. Vlookup is a standard answer but because there can be a max of 1000 jobs invoiced to us in any given month, this is a tedious task. I did try to record macro to find these jobs and return the month by making the colour of cell red but every time i run that macro it has run time errors and it doesnt change the format of the months and gets stuck basically.

I have attached the spreadsheet for help please.

Any help woud be grearly appreciated.

Thanks & Regards
Manav


Post your answer or comment

comments powered by Disqus
I need to compare data in two worksheets of an application for a given range
and need to display it in third worksheet.
But range has to be specified dynamically from Input box
--------------------------------------------------
Static Code


	VB:
	
 try1() 
     'This will compare the data in range A1 to D4 using EXACT but we are statically specifying the range
    Worksheets("Sheet3").Range(A1,D4).Formula = "=EXACT(Sheet1!A1,Sheet2!A1)" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
----------------------------------------------
Dynamic Code


	VB:
	
 try2() 
     'This will take the location of first and last cell from the Input box as String
     
    Dim a As String 
    Dim b As String 
    a = Application.InputBox("Please specify first cell", "First Cell") 
    b = Application.InputBox("Please specify last cell", "Last Cell") 
     
     'It doesn't work
     
    Worksheets("Sheet3").Range(a,b).Formula = "=EXACT(Sheet1!a,Sheet2!a)" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have to compare data in two reports to find the differences. The report
has approx 15 columns and 2500 rows, and I need to extract each entry that
has changed since the previous report. Each entry has an ID#, that may or
may not have been in both reports, or is in both reports but has differing
data in some of the columns. I am relatively new to using excel, so any help
at all is appreciated.

I have two columns of siganl names. Column A is "From" data. Column B is "To"
data. In some cases the dulpicates are the same signal path in reverse. An
example would be Col A: Pin_1 Col B: GND. Further down the list would be Col
A: GND Col B: Pin_1. I have 60,000+ rows of signal names. It would be great
to find the duplicates and eliminate doing twice the work.

Thanks
Dan

Each day I work with two worksheets. One is a list of numbers that is given
to me by someone else. The other list is something I make myself, again it is
a list of numbers. I need to know if there is a way I can compare the data to
find out if any of the numbers I input on my list match up to the ones on the
list I am given. I am aware of the "Find" function, but I'm working with
lists of hundreds of numbers, and it's really annoying to take each one and
"find" it on another worksheet. Any answers?

I have two columns of siganl names. Column A is "From" data. Column B is "To"
data. In some cases the dulpicates are the same signal path in reverse. An
example would be Col A: Pin_1 Col B: GND. Further down the list would be Col
A: GND Col B: Pin_1. I have 60,000+ rows of signal names. It would be great
to find the duplicates and eliminate doing twice the work.

Thanks
Dan

Each day I work with two worksheets. One is a list of numbers that is given
to me by someone else. The other list is something I make myself, again it is
a list of numbers. I need to know if there is a way I can compare the data to
find out if any of the numbers I input on my list match up to the ones on the
list I am given. I am aware of the "Find" function, but I'm working with
lists of hundreds of numbers, and it's really annoying to take each one and
"find" it on another worksheet. Any answers?

I have two worksheets of customers and I need to find out if the same person appears in both worksheets and put that persons information on a new worksheet. I'm sure this is an easy answer; but I just can't seem to find the solution as I'm not that experienced in this area.

Thank you very much for your assistance.

Hi all, thanks in advance for any assistance you can provide me.

Each month, I will be extracting a very large amount of data (about 30K records) from an in-house database into a .csv file. We will receive from an outside party, another .csv file with approximately 2000 records each month. Each of these files will have some columns in common, primarily first name, last name, address and phone number. They will not have the same number of columns or rows.

My task is to compare the first and last names in each sheet to look for matches. Then I will need to compare street addresses and finally phone numbers. It would be best if the matching data were flagged with a cell fill, but any means of denoting the matches would be fine. I do not yet have any sample data, but can substitute row and column letters and ramges where needed, based on what we get. It may be that one month there are no matches and the next month there are half a dozen or so.

The purpose of this is more of a "narrowing down" of the incoming data so we can perform a more intense check on the few data matches that are found each month. To that end, it is OK if a record is found with a matching street address, but the name and phone number don't match or vice versa. So if we find a Sam Jones on both lists, somebody will do a more intense search into our in-house data to determine if they are in fact the same person.

It could also be done by merging the data from both lists into a single worksheet and then doing a sort and search for duplicates. I'm just not sure how to go about it or which method would be easier.

hi all,

I am attempting to use conditional highlighting to highlight duplicate values in a range of data in excel 2007.

Here is exactly what I want:
Column A is a function of a 'toggle switch'. So if I change conditions of the toggle switch the values in column A change.

Columns B through N have static values in them. Some columns have duplicate values within them (i.e. column B has two entries for 'iphone')

I want cells within each column (B:N) to highlight when they match a value in column A.

Example:
Column A
iphone
Blackberry
Nokia
Column B
motorola
Ericsson
Samsung
Column C
LG
iPhone
Blackberry
Sanyo

Ideally, this formula would be backwards compatible with excel 2003.

I'm not familiar with VB so I need it to be a function.

I uploaded an example file to show you what I am looking to do.
In the worksheet called 'stage summary', i have it set up as follows:

Range A6:A150: values are a function of the cell A2 which can be toggled to one of several different options. (example: select 'unclassified' from the drop-down menu in A2 and you will have only 1 value in range A6:A150. If you select all, you'll have 132 entries).
Array B6:M150 are autopopulated cells taken from the worksheet 'Targets'. The cells do not change in response to changes in cell A2.

What I want, is for cells in the array B6:M150 to highlight red when they are duplicates of values in the range A6:A150.

PROBLEM: Some values appear more than once in array B6:M150 (example: Exa77 appears across several different stages and twice within one stage). I don't want these values highlighted unless they match a value in column A.

Thanks very much.

Hi

I'm an Excel newbie so bear with me!

I have an Excel file with two worksheets in it. Both worksheets contain a list of names and various other bits of data.

What I need to do is compare the names in the two worksheets and find all the ones in Worksheet A that do not appear in Worksheet B and copy them into a new Worksheet C.

There must be a way to do this in Excel but the Help menus aren't giving much away!

Does anyone have any ideas? I'd be really grateful!
Jude

I have a worksheet that has over 20,000 lines. What I need to do is comparing data in two different columns to find data that matches. When the matches are found ( the match could be in line 3 in column A and in line 10 in column B), identify the match and delete them. At the end the only data I must have are only the numbers in column B that there are no matches found in column A.

I've attached a sample of a worksheet for viewing. Please help. Thanks.

Chom

Hello good pple of the forum,
I’ve a workbook with two worksheet labeled new names and old names. There are some names present in the new name worksheet that are not present in the old name worksheet, pls is there a function or combination of functions that will enabled me compare the names in these worksheet to know which names doesn’t exist in the new name worksheet that are present in the old name worksheet.

I am a novice Excel user. I have to worksheets that have data, some of it is
similar (ie. Item #'s, etc.) I want to automatically compare the reports and
highlight data on one worksheet that is identicle to the data on the other
worksheet. If I can't do this, I'm going to have to manually compare the
data.

Hi All,

I am trying to compare data in two colomns, the data is registration numbers, my small set in AB and my longer list in CC. I need to see if registration number in CC can be found in the list AB. If it is there i just need it to say tru or false so I can then filter on the data.

Thanks

How do I compare data in two different spreadsheets i.e. (column 1 against
column 1)?

Hi,

I can't seem to find the correct way of doing this. I've tried VLOOKUP, IF MATCH, and others, but I just can't get it to work correctly. What can I say, I'm "always confused".

I have two worksheets (Sheet1 & Sheet2). Both have just one column of numeric data (Column A in both sheets). I want to compare the two worksheets to find matches and output the results for each row.

Specifically, I want to compare the numbers in Column A on Sheet2 with the numbers in Column A of Sheet1 and output the result to Column B (new) on Sheet2 with a 'Found' or 'Not Found' for each row.

I would prefer a formula over a macro if possible.

Thanks!

I have two sheets: Sheet1 with 8 columns and Sheet2 with 4 colums who is frequently update. How can Iwrite an Excel macro that needs to compare data in column A of Sheets 1with samecolumn of Sheet2, and when is find amatch then copy data of the corespondent rows in the Sheet2 . Thanks!

I am trying to find duplication by comparing two lists. One list is a subset of the other. In other words, if they were side by side, they would not line up because one list may contain 15 rows where the other would contain 60.

Would I use a combination of the index and match function? If so, how would it look?

Thank

Hello all, I'm having another VBA coding fit that I was hoping to get help with.

I am trying to copy raw data from two worksheets (Sheet2 = "550 Report (raw data)" & Sheet3 = "305 Report (raw data)"), that I cannot edit, over to a new worksheet (Sheet1 = "Exception Management") that I can edit. I have been successful in coding Sheet2 to come over correct, but am having problems with Sheet3 because of a few issues:

1st: Column A on both sheets contain a unique alpha-numeric identifier that can be used to identify records that can be combined. Sheet3 will sometimes contain multiple records for a single Sheet2 record.

2nd: Matching rows from Sheet3 will always be done based on Sheet2 column A (So I need to search through Sheet3 column A based on all values in Sheet2 column A).

3rd: The dataset is MUCH larger than my example files. Sheet2 normally contains 700+ rows of unique identifiers with 56 columns of data. Sheet3 is pretty much as-is, except ususally 1/10th the number of rows as Sheet2.

Here is the code I am experimenting with:


	VB:
	
 CommandButton1_Click() 
     
    Sheets("Exception Management").Select 
     
     ' Prepare Exception Management sheet for new data
     
    Cells.Select 
    Selection.Delete 
     
     ' Combine sorted worksheets onto Exception Management Sheet.
     
    Dim j As Long 
    Dim LastRow As Long 
     
     'Turn off screen updating and xlcalculation to  speed up macro
     
    On Error Resume Next 
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
         
        For Each ws In ActiveWorkbook.Worksheets 
             
            If ws.Name = "550 Report (raw data)" Then 
                LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _ 
                , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
                ws.UsedRange.Copy Worksheets("Exception Management").Range("A" & LastRow + 1) 
            End If 
             
            If ws.Name = "305 Report (raw data)" Then 
                 
                 'Insert Sheet3 code here
                 
            End If 
             
        Next ws 
         
         
         '         cycle through  sheets and  copy to sheet "Exception Management"
         '        For Each ws In ActiveWorkbook.Worksheets
         '
         '            If Not ws.Name = "Exception Management" Then
         '                LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _
         '                , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         '                ws.UsedRange.Copy Worksheets("Exception Management").Range("A" & LastRow + 1)
         '            End If
         '
         '        Next ws
         
         '        LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _
         '        , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         
         'delete blank rows
         
        For j = LastRow + 1 To 1 Step -1 
            If WorksheetFunction.CountA(Rows(j)) = 0 Then 
                Rows(j).EntireRow.Delete 
            End If 
        Next j 
         
         
         ' End of Macro Processing reset to default conditions
         
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
         
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The commented-out section of code is the only "successful" code that partially works. However, it only adds the sheet3 data below the copied sheet2 data...which does me no good whatsoever because I lose my Sheet3 Column headings.

On my Example "Exception Management" worksheet page, I manually formatted an example result that I'm trying to achieve with the code. I can easily do this formatting manually for a few single records using AutoFilter, but it becomes very time consuming when updating 70+ records every day.

I hope this is enough detail to start, Thanks. ~Dan Auto Merged Post Until 24 Hrs Passes;

Sorry, apparently my file attachment was lost. Here is the example file.

Hi there

I have two worksheets containing products arranged by product code.

I need to compare data in column A of the two worksheets, then if the data in Column A of worksheet 1 is NOT found in Column A of worksheet 2, the uncommon data should be pasted to Column A of worksheet 3.

Can anyone please advise how to do this - using VBA or otherwise?

(Basically, worksheet 1 is items we stock (around 5000 items); worksheet 2 is items from our supplier (around 13820 items). We need to isolate items that are in our stock which are no longer sold by the supplier).

Kind regards

Steve

Excel 2010

I have data in two different databases that i need to compare and report the differences. I can export to excel and cut n paste the part numbers in two columns.

Now I need to find part numbers in column "B" that are NOT in column "A" and visa versa.

Any help on this is greatly appreciated. there are over 5,000 items in each column.

John Lange
www.Stuff4Toys.com

I have 2 worksheets - Worksheet 1 (Paraphor) and Worksheet 2 (Products). In Worksheet 1 there are 9,000 rows and data in columns A through to G. In Worksheet 2 there are 5,000 rows with identical data in columns A to F. I need to find a way of pasting data from column G in Worksheet 1 to column G of Worksheet 2 where columns A-F in Worksheet 2 are equal to A-F in Worksheet 1

What is the best way of doing this?

Thanks

I have a spreadsheet with two colums. Each colum contains an identification
reference e.g.
Colum A Colum B
abc123456 nmg4568987
ahsj45236 abc123456
ikl7856489 jkh7854566589
I want excel to check if the items in colums b are also in colum a and
insert either "Match found" or "No match found" in colum c. For example
abc123456 nmg4568987 No match found
ahsj45236 abc123456 Match found
ikl7856489 jkh7854566589 No match found
Please help. I've tried everything I could think of. Nothing seems to work.

I have a spreadsheet with two colums. Each colum contains an identification
reference e.g.
Colum A Colum B
abc123456 nmg4568987
ahsj45236 abc123456
ikl7856489 jkh7854566589
I want excel to check if the items in colums b are also in colum a and
insert either "Match found" or "No match found" in colum c. For example
abc123456 nmg4568987 No match found
ahsj45236 abc123456 Match found
ikl7856489 jkh7854566589 No match found
Please help. I've tried everything I could think of. Nothing seems to work.


No luck finding an answer? You could always try Google.