Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

How To Match Two Sets Of Data Between Two Worksheets

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi I have two sets of data one in Sheet A & Other in Sheet B. I want to match these sets of data by taking Investment Name as an Unique Identity in an automated way(macro). The end result should be displayed as per sheet3 of my workbook. The workbook is attached. Please advise.

Thanking you in advance.

View Answers     

Similar Excel Tutorials

Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Combine Data from Multiple Worksheets in Excel
The easiest way to combine and consolidate data in Excel. Simple method to combine data from multiple worksheets i ...
How to Quickly Find Data Anywhere in Excel
Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & ...
Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof

Similar Topics

I'm looking to create a bar graph which compares two data sets. The two data sets have a column for a name and a column for a price. Unfortunately the names in the data sets don't always match up; as such, when I generate the chart the y-axis values do not match up with the x-axis labels. I have attached a sample book to illustrate what I am talking about. Thanks for any help. Cheers

Hey guys, I'm having a bit of a problem and its probably a quick fix, but every simple solution that I have tried seems to have failed. I have two workbooks with extremely large data sets (each book contains some 31,000 to 32,000 rows with about 25 columns). What I need to do is to compile all of that data into one workbook (all on one sheet) and auto filter the data. Now here is my problem: after I put my data onto one sheet and I try to auto filter the columns the auto filter only includes the values from the first data set (in filter drop down menu). The strange thing is that when I select a filter value it pulls all of the data on the sheet (from both data sets). Thus, I am confused as to why excel is not including the values unique to the second data set in the auto filter drop down menus, while it includes all of the data if the filter values are found in both data sets. I hope that my explanation isn't too convoluted; regardless, I would greatly appreciate any help. Cheers

Hello - I have two sets of data that I have been manually matching up in order to find their variance. It takes forever since I do this multiple times a day! Is there a Macro or Formula that will match up these two sets of data?

Sometimes the data doesn't repeat so the formula/macro would need to shift the cells down to line everything up.

I attached the raw data and the report I create as an example. Thanks for the help!

ok I need help on a formula that picks unique number sets example:


result = 256 , 789, 133
as you can see doubles are list onces.
I currently am trying to use match and sort....any related help would be great


Thanks for reading my Thread.

The problem I have is as follows:

1) 3 data sets 1440 rows each with 12 blank spaces inbetween down 1 column
2) I like to sum every 12 months for the 3 data sets
3) I figured out the formula for the first data set
4) Am trying to use the subtotal function with sum and and an array as the row reference for the 3 data sets but it is not working
5) My workbook is attached

Thanks for your help John

I have two sets of data; one is comprehensive and refers to all individuals in the second set, the second is incomplete but has an additional piece of information for the individuals it references that I would like added to the larger set where it exists.

Individual properties are referred to by two cells that are common to both sets of data (i.e. a postal code and a property number). The first list is of all properties, the second list is only of properties for which I have a third piece of data. I would like to add this third data point added into the first list when the postal code and property number match.


First list:
AB11AA 1
AB11AA 2
AB11AA 3
AB11AA ...n
AB11AB 1
AB11AB 2
AB11AB 3
AB11AB ...n

Second list:
AB11AA 2 1234
AB11AB 1 3214
AB11AB 2 3142

To become:
AB11AA 1
AB11AA 2 1234
AB11AA 3
AB11AA ...n
AB11AB 1 3214
AB11AB 2 3142
AB11AB 3
AB11AB ...n

My understanding of VLOOKUP is that it would only work if I was matching on one unique cell in each rather than attempting to match two cells in each that make up the identifier. I have attached an example workbook which hopefully makes clear what I would like to do; the first sheet contains an example of my data, the second sheet shows what I would like it to become.

Many thanks,


Hi there,

Sorry for my request for help here but I assume I'm a victim of my limited knowledge.

I'm building a league table for my Darts-Club. So far I progressed quite well and all the results and their corresponding points are calculated and displayed right where they should and the macros are working well.

The only problem I have is to find a formula to display the Wins/Losses/Draws in my League table just by entering the results in the corresponding games.

I have a league table sheet and a match result sheet. The match result sheet looks like this:

Column B =Team1, Column C =Team2, Column D =Sets won(Team1), Column E = Sets won(Team2), Column F=Points for sets won(Team1), Column E=Points for sets won(Team2)

This goes on for 132 rows/games. Now I need the formula to extract the Wins/Losses/Draws for each specific team. I tried the SUMIF but I have 2 criteria here which I somehow can't implement. Then I tried the IF function and I partially succeeded but the formula got so long that it gave me the "Formula too long" error.

Basically I want the following: If a specific team plays, the outcome will automatically be detected and displayed in Cell1 for Win, or Cell2 for Loss, or Cell 3 for Draw in my league table sheet.

Actually this seems not too difficult ... but I failed I think this has to be done with a macro.

I have two large data sets that I'm trying to combine (see attached example-Sheet1). The first data set contains a sample name with an associated value and s.d.. The second data set is a subset of the first where some of the samples have other data associated with it multiple times. What I'm trying to do is end up with a worksheet (Sheet2) where I have removed data that is not in both sets and also aligned the data. In addition, I want to add a new column that gives me the number of data points for each sample in the second set. Any ideas?

Hi, I would like to know if there is a method of creating a graph with multiple sets of data for the Y-axis, and with different sets of data for the x-axis... to illustrate
data set a
column1 column2
20.20821 -0.2067743
20.20700 -0.2089011
20.20879 -0.2110612

then data set b

20.20884 -0.2144541
20.20830 -0.2162071
20.20888 -0.2175114

I have a set of data that can be divided into sub-sets. I've attached the sheet of data. There are 5 sub-sets and each of these has 5 sub-sets. The sets are defined by the Portfolio and End columns.

So, the 25 sub-sets look like:

10 LCG
20 LCG
30 LCG

I'm wondering if there is a way to write a formula in cell N10 that basically pulls in '10' and 'INTL' and finds the correlation on only that sub-set of data. So I guess I want a formula that returns a range such that =correl(Range1, Range2) will work. Also, the length of the ranges will vary, so trying to write an INDIRECT(ADDRESS... will not work. I tried an array-formula in cell N10, but it didn't work. I also included my expected output. in M18:O24.

I know I can do this in other ways, but I really want to learn if there is a way I can do this with 1 formula that I can just drag down. I guess its more of a learning opportunity than something that needs to get done.


Hello, I'm somewhat new at this, well, it's been a long time since i've had to do this anyway,

I need help with something I need to do for my job. We have two sets of data, which have been sorted by a sequnce number (seqno) What we need done is to see if the second set, called 12D, has a match in the first set, 08D. If it does, we need to copy all the information in that data set from 8D, bascily columns A-I, next to the information from the 12D data set, so we can manual compare them. I think a macro would be best here, but not sure, can anyone help?

Hello all at work we use excel to make charts for phase matching antenna sets. We currently by hand put the data in the sheets and look at the sheets to see if they are a close match by the graph. We have five individual lines of different colors and they are supposed to lay and track close to each other. It is a very big headache to manually put the seemingly like data in the sheets over and over til we get a good match. To sum it up it takes hours if we have a lot of data to try to match up. So i've been trying to see if the process can be automated so any data I place in a cell will be matched will like data in another cell and displayed in the chart. I'm sure this can be done but I'm not sure how to do it. If anybody has any suggestions or help it will really be appreciated! I will check tomorrow and see what kind of data format we are importing into excel and post back. It comes from a agilent network analyzer. Thanks in advance!

Hi all,
I have data sets of X and Y arranged in rows. the first half coulmn data is the X and remaining half coulmns data are the Y..likewise, every row has even number of columns of which 1st half is the X and second half is the Y...

For example, I have data sets like

2 3 4 5 6 7 12 34 32 56 12 11
1 3 2 4 9 6 11 23 12 45 12 12
1 5 5 12 21 45
2 6 7 8 22 43 56 11

please note that length of X and Y pairs are not same for all rows .......
as shown in the above example set say for 1st row, 1st six values are X and remaining six values are Y...this follows for every row......

I need to fit these data sets using power law and to return only R2 (fittting coefficient) of the fitting...
Can anyone come up with either macro or any formuale so that I can calculate the R2 for all rows........I have thousands of rows of data sets like above pattern.

Your expertise and quick reply will be higly appreciated.......

best regards, Niranjan

I took a visual basic class in high school and pretty much don't remember how to write any code. I can record a macro fine, but when it comes to a blank module screen, yeah right. I have a data analysis that I'm trying to perform on two sets of data. I have attached the excel sheet here to make it easier.

Basically, Data 1 and Data two need to be analyzed and displayed on a "results" tab. Both data sets will be analyzed the same way. I need to compare the first three columns, Confidence Level, Sequence, and Protein Group Accessions. For instance, if A2 = A3, B2 = B3, and C2 = C3, then that would be considered a "hit" for my research. I need to find any duplicates within these three columns. I do NOT care about singles. If it is a single, it might as well be erased from my sheet.

Once the duplicates are found, possibly 5 or more matches, I want to average the Isolation Inerference, RT[min], and 113 column. Then, I would like to report this data on a results tab listing the duplicate value once with the average of all the duplicates in the set.

This data analysis would be done twice, once for Data 1 and once for Data 2. The results tab would have 9 columns - the first three columns, and results for data 1 (3 columns), and results for data 2 (another 3 columns).

I could be doing this for up to 15000 data points. Basically, I know how to accomplish this with formulas such as =COUNTIF("":"","") = 1, then sorting, then using AVERAGEIF, then VLOOKUP to match them on the final page. I just want to try to get some code for this because I need to do this data analysis with different sets of data for the next 4 years, so have a one button completes all would be nice.

Let me know if you have questions! Any sample of code, partial solution, or even full solution of code (which would be awesome!) would be much appreciated.test.xlsx

Ok, this is kindof a long explanation:

I have two worksheets with data that kind of looks like this:

Sheet 1

Sheet 2

Now what I want to do is combine this sheet into A/B/C/D/E/F/G/H/I

Complicating matters is the fact that I have had to go and custom sort Sheet 1 so now the order of the data in Sheet 1 and 2's 'A' does not line up

What I need is to find a way to go through each cell in Sheet 1, column A and then match it against Sheet 2, column A and then populate the data from F-I into Sheet 1

I'm not sure if a macro or a formula will do it... Help!


Help! I am having trouble coming up with a formula that can do the following:

Situation: Comparing two sets of data set up next to eachother that are only separated by a blank column. I have to first search for a matching Req # between the two sets of data. If I find a matching Req # then I now have to see if the status match. If the Req# and Status' match, then me a value of False, if the don't then give me a value of True(Example of data spreadsheet is below)

1st Task: See if there is any "ET&E Req #" from the far right set of data that matchs any of the "REQ#" from the far left set of data.
Resolution: I figured this out pretty simple using ISNA and Match formula of =ISNA(MATCH(G6,$A$2:$A$7,FALSE))

2nd Task: I now need to do the following: If value of false comes up from Task 1 then I now need to see if the associating Status' match. Tricky part here is the difference in terminology used among the two sets of Status'. I.e. "PASSED" in far left set of data is equivalent to "Pass" in the far right set of data.

REQ# PPS PARAGRAPH Status ET&E Req # Paragraph Level Test Status 100 PASSED 101 Fail 101 NOT TESTED 103 Unavailable 102 DELETED 105 Delivered 103 DELETED(CRLF)2008-07-22 104 Deleted 104 UNAV 100 Pass 105 HOLD-DATA 102 Hold/Data

Any Help??????

I don't even know how to search for this. I have a list of data by customer name. Each name has several rows of data. I manually copy the tab, delete all rows containing data for names other than the first, then save that tab. I have to repeat this process for 25 or so unique names or sets of data so that I have a workbook that contains tabs named for the customer with only their information contained on that tab. There has got to be a way to automate this with a macro, but I don't know how to tell excel go to the next cell where the name does not match and delete it. Any ideas?

Hi all,
I have data sets of X and Y arranged in rows. the first half coulmn data is the X and remaining half coulmns data are the Y..likewise, every row has even number of columns of which 1st half is the X and second half is the Y...

For example, I have data sets like

2 3 4 5 6 7 12 34 32 56 12 11
1 3 2 4 9 6 11 23 12 45 12 12
1 5 5 12 21 45
2 6 7 8 22 43 56 11

please note that length of X and Y pairs are not same for all rows .......
as shown in the above example set say for 1st row, 1st six values are X and remaining six values are Y...this follows for every row......

I need to fit these data sets using power law and to return only R2 (fittting coefficient) of the fitting...
Can anyone come up with either macro or any formuale so that I can calculate the R2 for all rows........I have thousands of rows of data sets like above pattern.

Your expertise and quick reply will be higly appreciated.......

best regards, Niranjan

I have two sets of XYZ data like this

X     Y     Z     and     X     Y     Z
0.1   1     1               0.1    1     -1
0.2   1     2               0.2    1     -2
0.3   1     3               0.4    1     -2.9
0.4   1     4               0.45   1     -4.5
0.5   1     5               0.6    1     -6
0.6   1     6

Now I need to superposition these lines and if possible extract sets of XYZ data for the result line

eg... Code:






the problem here is, the X steps are different size which results in different number of rows even with same range of X data....ANY IDEAS please?

Hi Excel gurus out there. So here is my situation.

I am working with data that comes in sets that is two cells wide and anywhere from 2-5 rows in height.
These sets are separated by one space in between them.
Ultimately, what I want to achieve is to remove any duplicate sets of these combinations. The most straightforward way I thought to do this would be to take these sets and lay them out in a single row (seen on the right side of my screenshot).
Remove duplicates should work fine if I am able to achieve this. I am not sure if there is an easier alternative.

New Microsoft Excel Worksheet.xlsx

I have 1 spreadsheet. Basically it's time series data (x-axis = time, y-axis = $). Let's say that over time, the $ values go up. However, at certain points, the $ re-sets and starts over again. So, if you were to connect the dots, it'd look like a sawtooth pattern. Also, when re-setting it doesn't re-set to zero. Let's just say it re-sets to 25% of the value when it re-set. So, if the $ goes to 100,000 and then re-sets, it re-sets to 25,000.

My question is, i'd like to draw a trendline, but treat each climb of dollars like its own separate trendline. So, if i have $ going up and re-setting 5 times, that would be 5 different trendlines.

This does not have to be an automated process; i'll just re-draw the trendline manually every time the $ re-sets. Ideally, i'd like to just use the SHIFT button and select the data points on the graph that i'd like to trendline, but excel doesn't allow me to select only certain points in the scatterplot. Is this possible? Thanks.

I am working on a project to Check my Lottery #'s that I have a subscription for. Currently I have it set up well to check the #'s using conditional formating. If you are familiar with Lotto Max, you may be able to help me.

So, I have my set of 7 #'s in 7 Cells. I enter the drawn #'s into 7 other cells and the bonus into another. When there is a match of the #'s, mine turn green. If the bonus matches it turns red. I have this working.

In another cell on the same row, is there a way I can count the cells that are green and another that counts the reds. That way I could see if I got 4 out of 7 and 1 bonus for example.

Max Millions. Since I have let say 10 sets of #'s in my subscription. If there are 20 Max Millions sets of #'s, how could i compare all my sets to the Drawn sets and be able to know if I have a set that completely matches?

Thanks for taking the time to look at this, I am learning a lot by doing this exercise.


What's the best way to look at two sets of data in excel and make comparisons between them. For instance how many matches (and what are the matches). Workbook attached as an example.

matches between data.xlsx

I am trying to create a chart that compares two different sets of data, one is $ the other is people. The x axis is time. Is there a way to make one chart out of these two different data sets? A related question is how to you control the units on the axes? If you look at the second chart attached, the unit increments are too great and do not show the data in a useful way. Any help would be much appreciated!

I once posted about this a little while back, asking for something that does sets and legs for darts, I was answered and was able to do it with help from borgem. Unfortunately my computer went all pete tong and I lost my documents. So I was wondering if someone could re-help me.
I would rather have formulas to do it, but if not i'll try with code.
Can anyone create me a sets and legs for darts.

For those who don't know how dart sets and legs work.
When someone gets to 0 from 501, they win a leg, once they have won 3 legs, they have won a set.
For example:
Player 1 wins a game (+1 leg)
Player 1 wins a game (+1 leg)
Player 1 wins a game (+1 leg)(+1 set)

As soon as player 1 recieves its 3rd leg it instantly turns into 1 set and sets the legs back to 0.
If they do the same and win 3 more legs, they will now have 2 sets.
Seen like:
Sets Legs
[Player 1] 2 2
[Player 2] 1 2

In order to add a leg, I will probably need a macro button probably sets Player 1 Win and Player 2 Win.
So once I click it, it adds a leg.

I hope you understand how i've explained it, and are able to find a solution for me.

- Darter