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.

Related Results

Two data sets, one average

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.

Merging Two Data Sets into One Table

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!

Sorting two data sets on the same worksheet

Hi,

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).

Compare two data sets

hi,

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.

Thanks,
Vijay

Merging 2 Data sets without a common column

Dear all,

I'll get straight to the point:

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

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

AFGHANISTAN KABUL 9320
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 TIRANA 3554
Albania-Mobile-AMC 35568
Albania-Vodafone Mobile 35569

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

AFGHANISTAN KABUL 0.0986
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 TIRANA 0.0180
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:

(3)
AFGHANISTAN KABUL 9320 0.0986
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?!

Thanks
Tom

Correlation matrix between two data sets

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

Graphing two data sets

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:
77,65,88,56,90

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.

Charting two data sets that dont completely match up

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

Trying to find the P-Value when correlating two data sets?

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

Smooth Data Set Of Chart

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.
thanks!

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,

Correlation coefficient between two data sets

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.

=CORREL(H2:AL2,RxPwrDiff!G537:AK537)

please find sampled sheet it making correlation with RxPwrDiff worksheet.

Thanks

Plotting the difference of two data sets

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.

Thanks

Brendan

Merging incomplete data sets by matching two cells in each

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.

e.g.

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,

QB

Compiling Two Data Sets and Applying Auto Filter

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

VBA Code to Differentiate between two Datasets using one data field and copying out.

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.

A Frustrated Newbie.

Merge two data sheets into one

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.

Thanks,

Nicko

Merging text from two columns into one column

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.

WolfLover

STDEV if with two data sets

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"

How to combine two data source into one pivot table

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?

Excel graphs - two data sets on the same y-axis?

Hi,

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
together!

Thanks!

Merging data sets

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?
Matrix121
File A.jpgFile B.JPG

Merge two tables in a new one

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

T2
Name Web
A www.a.com
B www.b.com
C www.c.com
D www.d.com

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

Name Product Qty Web
A Z 1 www.a.com
B Y 2 www.b.com
C X 3 www.c.com
D W 4 www.d.com

Hope I was not confusing.