Free Microsoft Excel 2013 Quick Reference

merge two data sets one unique

I am attempting to merge two excel worksheets. One has a set of unique ID
numbers with accompanying data. The other has ID duplicative numbers, but I
want corresponding info to be copied as appropriate.

Post your answer or comment

comments powered by Disqus
Hi all,

I am trying to chart two data sets on a single graph. Using XY scatter, I
can easily graph the data sets (X is time and Y is value in $). My question
is, is there a way to add a trendline that will track the moving average of
the two data sets over time? I have thought that perhaps I need to use a
combination graph, but wanted to hear what the experts out there thought.

Thanks for your help.

I have two sets of data, one from the November 2000 election and another from the November 2004 election. Each set has the same exact categories (Population, Citizen, Registered to Vote, Voted, etc) but with obviously different number values. I need to combine both data sets into one table to do a comparison and calculate the change from 2000 to 2004. I am not sure of the logistics of creating one table with both sets. Can anyone give me some suggestions? Thanks!


I'm looking to sort two data sets that are on the same worksheet. Both the data sets have the same 5 header columns just different years.

I need to find the most occurring ones (column5) then sort by same description (column 2) then by different years (column 3).


I have a requirement as follows. I have two data sets. Data set 1 and 2. i have more coverage in data set 1 and less in 2. I need to compare whether both the data sets have the same data. I need to have two conditions, i,e one for first column and second is second condition. I have attched a spreadsheet with sample data.
The joining conditions are as follows.

a. Check for Both the geos are same(fisrt column to first column)
b. If same, look for Cash Reserve column and check that whether they are same or not(Second column to secolumn)
c. Consider 2nd data set as base and bring in or highlight which are not equl

Please find the attachment with sample data.


Dear all,

I'll get straight to the point:

These are samples from my two data sets.....

(1) two columns........

Afghanistan Mobile-AWCC 9370
Afghanistan Mobile-Other 9371
Afghanistan Mobile-Other 9372
Afghanistan Mobile-Other 9375
Afghanistan Mobile-Roshan 9379
Afghanistan-Proper 93
Aland Islands 35818
Albania 355
Albania Mobile-Other 3553
Albania Mobile-Other 3556
Albania-Mobile-AMC 35568
Albania-Vodafone Mobile 35569

and (2), two columns...........

Afghanistan Mobile-AWCC 0.0995
Afghanistan Mobile-Other 0.0995
Afghanistan Mobile-Roshan 0.0995
Afghanistan-Proper 0.0986
Aland Islands 0.0119
Albania 0.0317
Albania Mobile-Other 0.0770
Albania-Mobile-AMC 0.0770
Albania-Vodafone Mobile 0.0770

the names inputed into the first column are in the SAME format in each data set, but there is NOT the same NUMBER of occurences in each data set which has caused me troubles when I try and merge the data.
I would like to be able to merge the data like this:

Afghanistan Mobile-AWCC 9370 0.0995
Afghanistan Mobile-Other 9371 0.0995
Afghanistan Mobile-Other 9372 0.0995
Afghanistan Mobile-Other 9375 0.0995
Afghanistan Mobile-Roshan 9379 0.0995
Afghanistan-Proper 93 0.0986
Aland Islands 35818 0.0119
Albania 355 0.0317
Albania Mobile-Other 3553 0.0770
Albania Mobile-Other 3556 0.0770
ALBANIA TIRANA 3554 0.0180
Albania-Mobile-AMC 35568 0.0770
Albania-Vodafone Mobile 35569 0.0770

so, for example where I have "Afghanistan Mobile-Other" occuring three times in my initial data set (9371, 9372, 9375), I would like ALL three of these to similarly appear in my final data set, even though the value they correspond to (0.0995 in data set 2) is the same.

I haven't been able to work this with the standard "merge workbooks" function. Is it possible this way or do I need to try another line of attack?!

Please help me, there is precious little hair still attached to my scalp!


Hi all, please help,

Lets say we have two data sets: 1st consists of 100 variables arranged into 100 columns and 2nd one is 200 other variables arranged into 200 columns. We need to calculate correlations of each of 100 variables with every one out of 2nd 200 variables. All variables for the same sample of 50 dates - so per every date we have 100+200=300 variables.So no need in correlations inside the groups, only cross correlations.At the output must be 100*200=20000 correlations. Is it possible some how just to write formula using correl and just drag it?? Any other way will be welcome. Is there any elegant and efficient way of doing it, say, in 1 min??? THANKS IN ADVANCE

I'm attempting to graph the probability of each outcome of adding one number from each of two data sets.

For instance:
Data set One:

Data set two:
58, 65, 70, 100, 80

So I want the possible outcomes of adding one number from data set one to one number from data set two. I remember doing something like this in High School, but I've lost the name for it, and forgotten how to do it.

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

I am trying to find the statistical sig between two data sets in excel and dont know how. We are trying to find the correlation between the two sets and see if this correlation is statically significant. Please help. Thanks

I have a chart with two data sets. One has monthly data (each month is a 3 month rolling average) and the other data is quarterly data. I have been asked to "smooth" the data on the quarterly data set but have been unable to do so. Can someone take a look at the attached graph and data and see if there is a way to plot this information? Is it possible even to have two x-axis? Would that be better -- or is there a way to smooth this data set.

Hi All,

I am really feeling guilty for not able to solve this simple problem. I want to match two data sets. The problem follows.

I have my data sets in "sheet 2". I have another data set in "Output". I want to add variables from the "Sheet 2". But I want to add data on my "Output" sheet (all companies) from "sheet 2". Which function will be best Vlookup, Index ??

I have added example sheet for this.

Thanks and Regards,

Hi Team,

I have a excel work sheet and it i am using this formula in sheet ( ACO , Cell H2) to get the relation between two data sets but when we are dragging this formula its not working or not making actual correlation.

Could You Please explain how this formula is working here so that i can use it in my other work sheet as well.


please find sampled sheet it making correlation with RxPwrDiff worksheet.


I have two data sets with overlapping ranges, both have a lot of common points and are very long. Is there a way of plotting the difference between them.

Note as the functions that generated them are highly nonlinear, I cannot simpy use the equation of the curves.



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,


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

Hey all,

First post so apologies if coming across as a newbie but the site is great.

Tried to find this myself but not working so thought I'd bite the bullet and post.

I have two data sets A and B with the same number of data fields, 8. (one is the data set from today, the other is the day previous).

The data-fields contain both text and numerical data.

Basically I am trying to write a macro to find the data that is in A and not in B, (using a unique numerical code number in column D, "REF"),

and copying this (ie the full 8 data fields) into a new sheet.

I am also looking to find the data that is in B but not in A (again, using the "REF" user field) and also copying into the another new sheet.

This will be a daily function so looking to get it automated with a macro button.

I have posted the data and also the what I am looking for the output, I had to use bogus data unfortunately as slightly sensitive.

Thanks in advance,

A Frustrated Newbie.

Hi all,

I have a manual process I have to do every month and I want to try and automate the procedure.

I have attached an example to make it easier to explain.

I have two tables of data, both in the same format (sheet 1 and sheet 2) with names down the left, Sites across the top, and values in the cells under the respective headings.

Is it possible to create a macro that will merge the two tables into one, like I have done in sheet 3? so i have all sites across the top, all suppliers in one column down th left and all values copied across.

I dont even know where to start on this, so if anyone has any ideas it would be much appreciated.



Hello everyone! I've searched and searched for tutorials about this and cannot find anything that works.

I am trying to merge two columns into one column. I have selected the two columns (also tried as experiment to select just two cells in a row). Then when I click on the Merge and Center button, I get the following message: "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." And it does!

I've tried to copy and paste the one cell into the other cell (which I've done many other times but it will not allow me to do so. I click on copy one cell, then highlight the other cell that I want to paste into then suddenly the paste function is not highlighted. I tried many times. Does not matter if I try to paste into the cell itself or the address bar area at the top. I do not want to replace the data in one cell with another, I want to add the data from one cell to merge the two cells together into one cell.

The data contains both text and numbers that I want to merge as follows:
(CELL 1) Size:Small:1:1:0:0:0::|Size:Medium:2:0:0:0:0::|Size:Large:3:0:0:0:0::|Size:XLarge:4:0:0:0:0::|

Here is (CELL 2) Color:Black/Pink:1:1:0:0:0::|Color:Pink:2:0:0:0:0::

These are attributes of products I'm trying to load into my ecommerce store. The way this shopping cart solution works is if you have multiple attributes such as above, (size and color choice), in order for it to show both choices to the customer, it must be all in one column. I did not know this at the time and made an additional column, one has the size attributes and the other has the color attributes.

What I am looking for is a way to merge the two columns into one single column and NOT lose anything out of either column. I can put the two data sets into the same column in either order. Does not matter if size or colors are first, I am just looking for a way to do this all at one time and not have to go in and re-type every one of them individually which is a huge hassle.

Thank you in advance for any help.


Hey everyone I have been trying to find a way to create a formula to act like a stdevif function. I have found many examples on how to do it on this forum when there is only one data set, but I haven't seen any with two. Or maybe I am simply unable to understand how to apply this formula

=STDEV( IF(ISNUMBER(DataRange), IF(DataRange < D8, DataRange)))

Let me better explain I have a row of % returns and then a row of ratios what i would like to do is create a formula that would act like this: STDEVIF(ratio"

I have an actual sales data source, and budget sales data source, i need to
compare actual vs budget years, is there a way to merge two data source into
one pivot table?


I'm trying to display two sets of data, with identical X-axis on the same
Y-axis, but am finding it hard. I don't want 4 bars for each X value, but a
graph with 4 X-catergories containing 2 bars each, then an identical x-axis
with a further similar data set (i.e. 4 x 2 bars) - anyone have a clue how to
do this? I've tried just making two graphs and deleting the Y-axis from one,
but the X-axis never line up in something like Powerpoint when I put them


I have two data sets data set A:
File A(data set 1) and File B (data set 2)
I want to merge these two data sets. The information in File A can go on to thousands( I have shown only wheat and meslin, there are actually 2000 products between same countries) but the information on File B remains the same. I wonder if this kind of data merging could be done in excel?
Thanking you in advance,
File A.jpgFile B.JPG

Hi folks,

can anyone help me in merging two tables into one without using add in or other external software?

Example: I have this two tables:

T1 (my master table)
Name Product Qty
A Z 1
B Y 2
C X 3
D W 4

Name Web

I'd like a new table with merge data from two two above:

Name Product Qty Web
A Z 1
B Y 2
C X 3
D W 4

Hope I was not confusing.

Thanks for your support

I have two worsheets and need to merge into one database. They both have the same fields (columns a-x) and I will use the customer number to merge the data. Worksheet A is an updated customer list, while worksheet B is an old database with some valuable information (email address, etc). Worksheet A has a total of 389 customers while worksheet B has 189. I vlookup the customers from B on A and found 88 matches. I need to transfer /merge the matched customer's data from worsheet B to worksheet A.

I am not an excel guru, so I would appreciate detailed instructions on how to get it done


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