Free Microsoft Excel 2013 Quick Reference

Comparing and matching data on two different spreadsheets

Hi,

I am trying to complete a mailing list, and want to match data from two spreadsheets.

Sheet1 contains a list of all my contacts, and Sheet2 contains a list of some of these contacts, the ones I do not want to include in my mailing. I basically want to remove all the contacts on Sheet2 from Sheet1, how do I do this?

Grateful for any help.


Post your answer or comment

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

I'm sure that there is an excel function that can do this but darned if I can figure out which one it is. This is what I would like to do:

I have two different workbooks. Each workbook has one worksheet that has two columns of data. The first column is a part number and the second column is a price. The first workbook holds my master data. Starting at the first column of the first row on the second workbook I wish to compare the part number held in that cell with the part numbers held in the first column of the first workbook. If there is a match then I want to take the associated price from the second workbook and place it in a cell to the right of the corresponding part number in the first book - actually in the first open cell in the third column. I wish to do this automatically via macro, or otherwise, for all part numbers in the second workbook. The macro will also need to recognize that some part numbers in the second workbook may not appear in the first workbook in which case that part number is skipped.

The end product is my first workbook that has been transformed from two columns of data to three columns of data - one part number and two price points although not all part numbers will have the two price points.

If it helps I can copy the data in the second workbook and paste it in to a second worksheet in the first workbook and do all the work in the one workbook.

Thanks in advance for all help.

Greetings to all....

I have scoured this and other forums for help with my problem to no avail. Thanks in advance for your patience and help with this. I am a -newer- excel user and have been teaching myself overtime.

So, I have huge list of raw data parsed into excel from an XML file.The XML file is updated continuously. The data is formatted like this when i open it in Excel.

village name | x coordinate | y coordinate | points | player | clan

I filtered the data by clan, which then gives me the total list of villages from that clan. I did this twice with two different clans and copied each set into two different sheets. I want to compare Clan A's data with Clan B's data, and display any identical matches on a new sheet, or just know how many village matches there are. The way I have been trying to compare and match the data is by using the x , y columns because those are the village identifiers and they never change.

I am trying to make the most sense out of my problem. I thought you needed a little background about the information before moving on to help me. Thank you for your time and help.

I need help in matching data from two excel spreadsheets. I am trying to
compare or match 5-25 lines with identical part numbers and description to a
another spreadsheet which has about 32000 lines of data.
Any help will be appreciated.
Thanks

Hello
I have been stuck for ages now trying to get excel to compare and match two sheets.
Basically I have two sets of different data on different sheets (from different sources) with one common piece, e.g I could have columns called Name Team Dept in Sheet A and then another with Name Age Height in sheet B.
What I need is to produce three more sheets, so I need a sheet that shows me the data that is A and B and also adds the data together, then one that shows data in A but not B and in B but not A.

I can do this manually by Advanced filtering backwards and forwards but I have thousands of records and am struggling.

I appreciate this might be very hard to do using functions but I have also been unable to find any software that matches my requirements, so any help would be appreciated.

Any help would be soooo appreciated.

Ta

Shaun

Hello Guys,

I am a newbee here and would like your help in one of the problem that I am having in excel.. actually I am not sure how to handle this problem..

I have two different spreadsheet which has duplicate addresses and unique addresses (which has both numbers and street address combined). I need to find the duplicates in both the spreadsheets (of 6500 rows) separately and after that match those duplicated addresses in both the spreadsheets. Once it is done then I have to find those duplicate address in a third spreadsheet (to find out if we have any common in that too) and after that put it on a different spreadsheet the result by counting the duplicate addresses in a table format....

Please let me know how should I do it..

in the process I would like to know.. how to highlight the duplicate address in 2003 and show only feature for see only highlighted cells in the spreadsheet.

When using autofilter I also need to clean up the spreadsheet by fixing addresses from ST. to STREET, Dr. to DRIVE so that I could get the exact info in autofilter which is very time consuming. Although I can do that in Find and replace but it takes forever as it changes the address which has RD or CIR like BattlefoRD road to BattlefoRoad etc. so what is the shortcut formula for that..

Guys I really need your help in this. thanks much

Hi All,

I'm new to this board and new to VBA. If you could point me in the right direction, it would be much appreciated.

I'm trying to compare two cells on two different worksheets and adding values into a column on one of the sheets.

Data is set up as follows:

WS #1 Invoices
A1: Acct# B1: ShipToCode C1: InvoiceAmt

WS#2 Accounts
A1: Acct# B1: ShipToCode C1: NewSalesTotal

I'm trying to match Acct#/ShipToCode and then add the InvoiceAmt to NewSalesTotal.

I'm good at cut/paste and modifying the code to my needs but am having problems finding something similar to this.

Thanks in advance,
Christine

Hi all, new to the forum here, and I've run into a bit of a bind.

I am looking to compare two different datasets, to check either with vba or a formula if information is contained. I came up with a scenario that would work similar to what I'm working for clarity, and also attached a dummy sheet with fake data to test/work on.

Sheet1 Sheet2
Ordernum Pmnt_dt Ordernum Ship_dt

I need a way to check if Ordernum has a Pmnt_dt, but no Ship_dt, and if not, to populate that Ordernum into a new sheet.

I've tried Vlookup and a couple other ideas (nested If's, OR, AND) but none of them seem to work. Maybe I'm not formatting my formulas correctly, but I need Help... Please!!

Thanks in advance!

I have two spreadsheets and I'm needing a little help!

In the first spreadsheet, I have a listing of companies in column a, and a listing of materials in column b.

In the second spreadsheet I have a listing of various bits of information which includes a listing of materials in column c.

I'm wanting to see if there is a formula that can be entered that will compare the materials on both spreadsheets and if any of the materials match within the range it populate the company name on the second spreadsheet.

Example;

Spreadsheet 1:
A1: Motion B1: 500111111
A2: Motion B2: 500222222
A3: SafetyNet B3: 500333333

Spreadsheet 2:
A1: Misc B1: Misc C1: 500222222
A1: Misc B1: Misc C1: 500333333
A1: Misc B1: Misc C1: 500111111

Needing the formula to populate column D on the second spreadsheet with the corresponding company in column A from the first spreadsheet.

Meaning if a material in columnC on spreadsheet2 matches a material in columnB on spreadsheet1 that it populate the company in columnD on the second spreadsheet from the information in columnA on spreadsheet1.

Any ideas?

Hi I have a problem matching and combing data from two sheets, one sheet1 has the following columns: startdate, starttime,artnr, serialnr, stepnumber,measurenr, measurepunkt och symptomcode.

Sheet2 has startdate, starttime, artnr, measurenr, measurepunkt, errorgroup and errornr.

What I want do is to compare startdate, starttime and artnr in sheet1 to startdate, starttime and artnr in sheet2

Data from sheet1: Sheet1 contains data in column A to H but it is only column A, B, C that I can use to match the data with sheet2 column A, B, C because that is the only information in common startdate, starttime and artnr. I have uploaded the data sheet1 and sheet2 to give a better description of my problem. This is only a part of the files because the sheet1 has 37000 rows of data and sheet2 has 10000 rows of data so I only uploaded a part from each to give a better understanding how the data is structured.

So im missing columns errornr and errorgroup in sheet1 and I want to compare every cell in column a to c in sheet1 to every cell in column a to c in sheet2 and if there is match add the missing data errorgroup and errornr from sheet2 to sheet1 in colum I and J.

Im Thankful for all help

Regards/ Pilezar_81

I work at an health insurance company and frequently need to reconcile
reports from our physicians with claim data. I need to find records on two
different spreadsheets that match on three data points and insert a claim
number on matching records. Here's an example:

Spreadsheet 1, from our physician group, contains treatment data for one
patient.

PhysicianName ServiceDate Procedure# $billed $paid deductible

Smith,John 12/01/03 99213 75.50 25.00 25.00
Doe,Mary 01/25/04 99215 125.00 90.00 0.00

Spreadsheet 2, from our server, contains claims data for one patient.

Claim# ServiceDate Procedure# $billed $paid deductible

123456789 12/01/03 99213 75.50 25.00 25.00
123687432 01/27/04 99215 125.00 90.00 0.00

I need to find records on spreadsheet 1 that exactly match spreadsheet 2 on
the fields ServiceDate, Procedure#, and $billed. When a match is found, I
need the corresponding Claim# inserted after the record on spreadsheet 1. So
on the example data above, the first row would be a match, because all three
relevant fields are the same. the second row is not a match, because the
ServiceDate field does not match.

I figure that I'll need a combination of MATCH and INDEX, but I haven't been
able to pin down exactly how to accomplish this. Any help you can give will
be very much appreciated.

I am a NOVICE in using Excel and need help in resolving this issue.
.
I am receiving a data from two different sources having multiple information.
I want to match the data for any missing entries from one sheet . There is a common column Field N which contains the reference number of the transaction, based on this reference number i require to match these entries.
e.g: Sheet A contains serial Number, name, ID Number (not common in both sheets) , amount , name and of course [B]Reference[ Number ( which is common in both the sheets.
.
I would like to match these two sheets on the basis of this reference number and if there is a MISMATCH of ( Reference number is NOT present in sheet 2 than this information should be COPIED in a SEPARATE work sheet.
I tried different VLOOKUP functions BUT am still unsuccessful. Please HELP/B]
.
Regards,

find duplicates in excel 2007 from two different spreadsheets. I have to modify a report weekly and I am looking for a way to search for duplicates without having to use CTL+F. Data is added to the master and I am saving it on my desktop and cutting and pasting.

Hi All,

I am new to Excel Macros and need experts Help, I was stuck up at Compare and copy data from one sheet to another sheet in Excel 2007.

I have a excel workbook which contains 3 sheets(sheet1 , sheet2 and sheet3). Sheet1 is master sheet of sheet2 and sheet3 means it will get data from these 2 sheets, so sheet1 columns are combination of sheet2 & sheet3 columns.

sheet2 & sheet3 are linked with different external sources and when ever changes will occur in external sources automatically these two sheets will get updated . so always these sheets are having latest data .

but sheet1 is getting data from sheet2 and sheet3, sheet1 is not updating every time when sheet3 got update ,since we are not automated the connection between sheet1 and sheet3.

our objective of this Macro is , it should compare the sheet1 and sheet3 based on Request Number(it is a unique and common column in both sheets) and needs to copy the whole row corresponding to the request number from sheet3 which does not exists in sheet1 and paste the data into corresponding columns in sheet1. we need to make sure that no duplicate values are occurred in Request Number in sheet1.( example sheet1 have 10 rows and sheet3 have 13 rows, in both sheets 10 rows are identical and remaining 3 rows added newly in sheet3. now we need to copy those 3 rows into sheet1 into corresponding coulmns)

here i have attached the macro code, it is working fine up to some content but not satisfying my requirement.

Any help would be highly appreciated.

thanks in advance.

Regards,
Mallesham

Hi,

I have the following data from two worksheets (the same workbook) that i am trying to compare and put the result into a third worksheet:

Worksheets 1:
ID Value
1001 10
1002 20
1003 30

Worksheet 2:
ID Value
1002 20
1003 30
1004 40

Worksheet 1 and 2 have about 6000 rows (or more) each to be compared. From the above worksheets I wanted to first Lookup the ID in worksheet 2 that are identical with worksheet 1, in this case 1002 and 1003 and then sum up the Value and put the ID and the added Values into worksheet 3, so the results should be:

worksheet 3:

1002 40
1003 60

I have tried to use the For Loop with the following code and the problem is it take so long for the loop to run (more than 20 minutes)..... I am not sure what is the result as I have terminated the run after 20 minutes. I know For loop is not that efficient, could someone point me to the correct direction as to what method should I use?


	VB:
	
 
count1 = WorksheetFunction.Countif(Worksheets(1).Column(1), ">0") 
count2 = WorksheetFunction.Countif(Worksheets(2).Column(1), ">0") 
 
For i = 1 To count1 
    For j = 1 To count2 
        If Worksheets(1).Cells(i,1).Value = Worksheets(2).Cells(j,1).Value Then 
            Worksheets(3).Cells(i,1).Value = Worksheets(1).Cells(i,1).Value 
            Worksheets(3).Cells(i,2).Value = Worksheets(1).Cells(i,1).Value + Worksheets(2).Cells(j,1).Value 
        End If 
    Next j 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any advice is much appreciated!

many thanks

Best Regards
Casey Wong

Match the entire data on two different worksheets and return the rows which are different in one of the worksheets.

I need to write a function in Excel to find matching data in 3 seperate
spreadsheets, column 1 so that I can view and compare the corresponding data
in column 5 of those 3 spreadsheets. I am hav9ing problems writing the
function: first phase
=VLOOKUP(Sheet1!A3:A1588,Sheet2!A:E,5,FALSE)

Hi

I have som problem matching data between two sheets.

Sheet A is where the data should be filled out based on raw data in sheet B. The only match I have is the JobID number. So If JobID in Sheet A and Sheet B match, copy the values in selected columns from sheet B to A

In some cases it is possible that the raw data (sheet B) has more JobID`s than in sheet B, and if that is true, I would like to add it as a new job in Sheet A (after last row). I have made an example in uploaded sheet that explains it more in details.

Thanks for any help!

Elad

I need to create a chart (line chart) data series, but based on two different
columns of data...

Is this possible ?

Hello,

I need to figure out a formula that will pull the most recent date from a calendar spreadsheet that I am using and place it on a different spreadsheet. If you look at the file I have attached you will see the calendar on the "Employee1" sheet. I need the most recent date that has hours in it (January 14 in this example) to show in B2 on Sheet1. The file I have attached is an example and I would use the formula on a much larger file with many calendars. I just need the core formula. Thank you.

I have first and last names on two different columns for both workbooks. Each individual has a different username which is also organized in another column but in only one of those two workbooks. Workbook 1 does not have the usernames. Workbook 2 has the usernames but also has extra individuals. Is there a macro that can compare the individuals and match the correct username from Workbook 2 to the individuals in Workbook 1. If that makes sense. Thanks

Help please i need to compare and match one column from another column to check the completeness of my column A from column B. The contents of the cell are address so they are all not 3 words in a cell they can go as long as 9 words. Also my column A and column B are not encoded exactly the same, like one address might lack and period or a comma or instead of using the word Star Mall Place in column A you can see in column B that this is just written as Star Place but this is still a match. can you help me please?

i dont know anything about codes nor formulas help please.

Here is a sample of what i am matching

column A
Brgy. Gayaman, Binmaley Pangasinan
Brgy., Biquig, Bantay Ilocos Sur
San Carlos City Pangasinan

column B
Brgy. Tocucan, Bontoc, Mt. Province
Chanog, Bontoc, Mt. Province
Lazaro St., Biquig, Bantay, Ilocos Sur

note than the 2nd item in my column A "Brgy., Biquig, Bantay Ilocos Sur" is a match in column B item 3 'Lazaro St., Biquig, Bantay, Ilocos Sur" although they are not typed exactly the same.

can you help me please? ive been trying to get help for a week now

Hello all - I'm trying to union two different ranges that exist on two different sheets and then copy the unique values into a third range with just the unique values. I was going to then use the unique range as my rowsource in a listbox. This is the code I was working with so far but i'm getting a compile error (method range of object failure). Any ideas

Code:
 
Private Sub UserForm_Initialize()
Dim range4 As Range
Dim range3 As Range
Dim range2 As Range
Dim range1 As Range
Set range1 = Worksheets(1).Range("MyRange")
Set range2 = Worksheets(2).Range("MyRange2")
Application.Union(Range("range1"), Range("range2")).Select
Selection = range3
range3.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=range4, Unique:=True
Me.lstone.RowSource = range4
End Sub


I can create a graph with two bar groups and one line on two differing vertical axes, but I want to create a graph with two lines and one bar group. How can I do this?

Example:
Year 1 Year 2 Year 3
Revenue 100 120 111
Profit 5 7 6
Movies 6 3 5

Where revenue would be a bar graph and profit and movies would be lines relating to a different vertical axis. Thanks.


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