How To Match Two Sets Of Data Between Two Worksheets 


How To Match Two Sets Of Data Between Two Worksheets  Excel 
View Answers 
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.
Thanking you in advance.
Similar Excel Tutorials
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 & ...
Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & ...
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 ...
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
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
 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
 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
 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
 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
 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 yaxis values do not match up with the xaxis labels. I have attached a sample book to illustrate what I am talking about. Thanks for any help. Cheers
Hi All
I have multiple rows of data which I have sorted by two identifiers. Where a set of data matches on both of these identifiers I would like to further compare the two rows based on a set of 8 criteria (in coulmns CJ). Where the rows match across both identifiers and across the 8 sets of criteria I would like to merge these into 1 row and copy to another sheet, where there is a mismatch on 1 of the 7 sets of criteria or if a row's 2 identifiers do not match another row I would like the data to remain as is (or move to another sheet for non matches).
Please see below example.
For Pair 1 & 2: these match across the 2 identifiers and across all 7 sets of criteria so these would be shaded white and moved to a different sheet (or the addition of match at the end would also suffice).
For pair 3: these match on IDs but not on all 7 criteria again "no match" would suffice here.
For the final row it does not match on ID therefor I would need to "no match".
Can anyone suggest a macro or any type of functions I could use?
Thanks a lot and please let me know if I can clarify anything
I have multiple rows of data which I have sorted by two identifiers. Where a set of data matches on both of these identifiers I would like to further compare the two rows based on a set of 8 criteria (in coulmns CJ). Where the rows match across both identifiers and across the 8 sets of criteria I would like to merge these into 1 row and copy to another sheet, where there is a mismatch on 1 of the 7 sets of criteria or if a row's 2 identifiers do not match another row I would like the data to remain as is (or move to another sheet for non matches).
Please see below example.
For Pair 1 & 2: these match across the 2 identifiers and across all 7 sets of criteria so these would be shaded white and moved to a different sheet (or the addition of match at the end would also suffice).
For pair 3: these match on IDs but not on all 7 criteria again "no match" would suffice here.
For the final row it does not match on ID therefor I would need to "no match".
Can anyone suggest a macro or any type of functions I could use?
Thanks a lot and please let me know if I can clarify anything
Hello,
I have a problem im trying to sort out. I am new to excel but i cannot work out this function:
I need two match to sets of data (two seperate worksheets) then display "error" if there is a miss match between the two sets of data on one worksheet.
1) I have two worksheets 1: MATCH and 2: ORIGINAL. these sheets both have the same data in each column. columns being Name, Address, Suburb. then listed under are all details.
2) On one the MATCH sheet there is one inconsistency and as a result am trying like to display "Error" if there is an inconsistency. ie: there is one address not matching the original.
Please see attached for an example:
My question is does somon know if a formulay that would work? i am new to excel and this would be interesting to know:
I have a problem im trying to sort out. I am new to excel but i cannot work out this function:
I need two match to sets of data (two seperate worksheets) then display "error" if there is a miss match between the two sets of data on one worksheet.
1) I have two worksheets 1: MATCH and 2: ORIGINAL. these sheets both have the same data in each column. columns being Name, Address, Suburb. then listed under are all details.
2) On one the MATCH sheet there is one inconsistency and as a result am trying like to display "Error" if there is an inconsistency. ie: there is one address not matching the original.
Please see attached for an example:
My question is does somon know if a formulay that would work? i am new to excel and this would be interesting to know:
Hello VBA gurus,
Is there a smart way to code vba when comparing two sets of values from two workbooks based on criteria?
For example:
Workbook 1:
8/11/2014 8184693
8/11/2014 8263872
8/12/2014 6988226
8/12/2014 8185067
Workbook 2:
8/11/2014 8184693
8/11/2014 8263872
8/12/2014 6988226
Workbook 2 shows the updated data, while workbook 1 data needs to be checked.
In checking, the macro needs to match the date and ID number in both workbooks. When a match is struck with both pieces of data, it continues checking the remaining data (no action taken). If the date in workbook 2 appears in workbook 1, and the ID number appears in workbook 1, but not in workbook 2, then I need to fill the entire row with a specific color in workbook 1 for that record (similar to above). There will not always be the same date range in both workbooks, but the dates will overlap at least a few days.
Additionally, does each workbook need to be sorted a certain way before the data is compared?
Have a great day and thank you in advance if anyone can write up a macro for this.
Is there a way in VBA to compare two sets of numbers to see if there is a match, and use each value only once? For example (imagine each number is in its own cell and there are 3 columns):
1 2 3 matches with 1 2 3
1 3 3 does not match with 1 2 3
1 2 3 matches with 1 3 2
I'm looping through worksheets in a workbook and comparing them to a column in a different workbook. I can probably handle the specifics, but need a little help with the technique.
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
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!
I would like to find matching pairs. If you have two tables  table1 with for sets of data (I, II, III, IV) with every data set containing five variables (Var1, Var2, Var3, Var4, Var5), and table2 with four sets of conditions (A, B, C, D) for every data set to match. Now, I want to make a macro to find a matching unique set of conditions in table2 for every unique set of data in table1, and this solution could be output in table3 (like for example: AIII, BI, CIV, DII).
Table2: set of conditions with its five variables:
A: 0; 4; Green; No; Number between 1 and 100
B: 1; 6; Red or Black; Yes; <90
C: 0; 8; White or Blue; No; >75
D: 1; 3; Red; Yes; Number between 50 and 79
Table1: set of data with its five variables:
I: 1; 6; Red; Yes; 77
II: 1; 3; Red; Yes; 55
III: 0; 4; Green; No; 65
IV: 0; 8; Blue; No; 80
The problem is solved only when pairs are formed for every set of data and every set of conditions. So, each set is used only once.
There could be more than one solution how to form pairs between the sets of data and sets of conditions. So more sulutions could be output in table4, table5, table6, and so forth.
Now for this to be useful to me I need to be able to extend this macro for using 150200 sets of data and the same amount of conditions, and perhaps 20 variables for every set.
I would be very happy for some help here.
I would like to find matching pairs. If you have two tables  table1 with for sets of data (I, II, III, IV) with every data set containing five variables (Var1, Var2, Var3, Var4, Var5), and table2 with four sets of conditions (A, B, C, D) for every data set to match. Now, I want to make a macro to find a matching unique set of conditions in table2 for every unique set of data in table1, and this solution could be output in table3 (like for example: AIII, BI, CIV, DII).
Table2: set of conditions with its five variables:
A: 0; 4; Green; No; Number between 1 and 100
B: 1; 6; Red or Black; Yes; <90
C: 0; 8; White or Blue; No; >75
D: 1; 3; Red; Yes; Number between 50 and 79
Table1: set of data with its five variables:
I: 1; 6; Red; Yes; 77
II: 1; 3; Red; Yes; 55
III: 0; 4; Green; No; 65
IV: 0; 8; Blue; No; 80
The problem is solved only when pairs are formed for every set of data and every set of conditions. So, each set is used only once.
There could be more than one solution how to form pairs between the sets of data and sets of conditions. So more sulutions could be output in table4, table5, table6, and so forth.
Now for this to be useful to me I need to be able to extend this macro for using 150200 sets of data and the same amount of conditions, and perhaps 20 variables for every set.
I would be very happy for some help here.
ok I need help on a formula that picks unique number sets example:
256
256
789
133
133
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
256
256
789
133
133
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
Hi,
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.
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
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
I have 2 sets of data sets, maybe 10,000 rows and about 25 columns,
Let's say column B is an identifying column, it contains, letters and numbers like AAA#####, BB##### or just numbers ######.
Their are 2 problems,
one  this is not a unique identifier, so I need another column say column C which contains only a string that together form a unique set.
two  their will be some data that is in the first data set and not the second, and vice versa, I need to know which it is
the main objective is to take that unique identifier and calculate the difference in columns J though N of both data sets and paste that in a new file.
I have created a macro that would loop through each item in the second data set, and then, loop through each item in the first, and look at the other columns and paste if it is the same, but this code takes like 5 minutes to run. and I wasn't able to solve the second problem as I use one data set to look at the other, so I wouldn't know if something was missing from the first data set.
Any help would be great.
Let's say column B is an identifying column, it contains, letters and numbers like AAA#####, BB##### or just numbers ######.
Their are 2 problems,
one  this is not a unique identifier, so I need another column say column C which contains only a string that together form a unique set.
two  their will be some data that is in the first data set and not the second, and vice versa, I need to know which it is
the main objective is to take that unique identifier and calculate the difference in columns J though N of both data sets and paste that in a new file.
I have created a macro that would loop through each item in the second data set, and then, loop through each item in the first, and look at the other columns and paste if it is the same, but this code takes like 5 minutes to run. and I wasn't able to solve the second problem as I use one data set to look at the other, so I wouldn't know if something was missing from the first data set.
Any help would be great.
I have two Excel data sets, each with several hundred records (lines in Excel). Each record has multiple fields (columns in Excel). In both sets, each record line contains an email address. Some of the same email addresses exist in both data bases.
I would like to merge the two datasets, retaining all the fields for records that have email addresses in both sets and removing those with emails that are unique to one or the other set. Basically the opposite of removing duplicates, in this case I want to retain all the data for the records that are common to both sets. (Reason being the two different data sets contain different information.)
For example, assume dataset #1 contains a row with ABC@pacbell.net plus three other fields/columns and dataset #2 contains a row with ABC@pacbell.net plus four fields and a row with XYZ@sbc.com plus four fields. I would like to merge them and end up with just one row of ABC@ pacbell.net plus all seven fields. XYZ would be eliminated.
I would like to merge the two datasets, retaining all the fields for records that have email addresses in both sets and removing those with emails that are unique to one or the other set. Basically the opposite of removing duplicates, in this case I want to retain all the data for the records that are common to both sets. (Reason being the two different data sets contain different information.)
For example, assume dataset #1 contains a row with ABC@pacbell.net plus three other fields/columns and dataset #2 contains a row with ABC@pacbell.net plus four fields and a row with XYZ@sbc.com plus four fields. I would like to merge them and end up with just one row of ABC@ pacbell.net plus all seven fields. XYZ would be eliminated.
First time posting so bear with me if I dont provide enough info.
I essentially have two sets of data I would like to compare to each other using 2 criteria. This is easy enough as you can make use of match index and this will provide the required result.
The problem, however is that both sets of data contains duplicates and i would like to match the 2 sets of data and generating a list with only the data not matched. The problem is that excel will search for a match in one set of data and if that match is duplicated it will continue to use that first match
Here is a simple example to illustrate ( letter refer to 1 criteria and the number to a second)
Data Set 1 Date Set 2 a 1 a 1 a 1 a 1 b 1 b 2 b 2 b 2 b 2 b 3 b 2 b 3
essentialy after matching i want to be able to indentify b 1 and b 2 from data set 1 as not being matched.
Therefore the b 2 in data set 1 is my probelm excel will run through data set 2 to find it however will use the b 2 in data set 2 for all 3 instances of b 2 in data set 1. To date i have found up to a 100 duplicates and i am dealing with around 20 000 lines of data or so on a monthly basis.
I have solved this problem by using a complex matrix that will eliminate these duplicate matches using a if inbedded match but unfortantly our computers at work here can not handle this matrix very well as currently i have to recreate this formula for every duplicate i want to elminate hence a 100 cells worth of formula's which makes calculating it brutally slow.
I was just wondering if there is a way you can tell excel to simply match on a 1 to 1 basis and not on a 1 to many.
Any help would be appreciated
Thanks
I essentially have two sets of data I would like to compare to each other using 2 criteria. This is easy enough as you can make use of match index and this will provide the required result.
The problem, however is that both sets of data contains duplicates and i would like to match the 2 sets of data and generating a list with only the data not matched. The problem is that excel will search for a match in one set of data and if that match is duplicated it will continue to use that first match
Here is a simple example to illustrate ( letter refer to 1 criteria and the number to a second)
Data Set 1 Date Set 2 a 1 a 1 a 1 a 1 b 1 b 2 b 2 b 2 b 2 b 3 b 2 b 3
essentialy after matching i want to be able to indentify b 1 and b 2 from data set 1 as not being matched.
Therefore the b 2 in data set 1 is my probelm excel will run through data set 2 to find it however will use the b 2 in data set 2 for all 3 instances of b 2 in data set 1. To date i have found up to a 100 duplicates and i am dealing with around 20 000 lines of data or so on a monthly basis.
I have solved this problem by using a complex matrix that will eliminate these duplicate matches using a if inbedded match but unfortantly our computers at work here can not handle this matrix very well as currently i have to recreate this formula for every duplicate i want to elminate hence a 100 cells worth of formula's which makes calculating it brutally slow.
I was just wondering if there is a way you can tell excel to simply match on a 1 to 1 basis and not on a 1 to many.
Any help would be appreciated
Thanks
I have 11 worksheets in a workbook containing two discrete sets of data.
8 worksheets contain one set of identical data.
3 worksheets contain a second set of unique, but identical data.
The redundancy exists because each sheet is sorted based on different criteria.
I made a very simple macro which:
1. Selects the entire row of the active cell
2. Shades the row a specific color
VB:
I can't determine how to shade the identical rows of data in the other worksheets. There is one unique "identifier" cell within each row of data, under the column header "ID." However, the unique identifier cell is in a different column for the two sets of data (i.e. in one set of data, the "ID" code is in column X, and in the other set of data, the "ID" code is in column AA).
Is it possible to search based on the column title/header, which is "ID" since that column title/header is maintained in the two sets of data?
I thought the approach should be:
1. Select the row of the active cell
2. Ask excel to search the active workbook, and select all rows which contain data matching the cell data within the current active row under the heading "ID"
3. Shade all those rows
If the specific column must be specified, i.e. search "X" or search "AA" then I can make two macros for the discrete sets of data. However, I can't even figure out how to
8 worksheets contain one set of identical data.
3 worksheets contain a second set of unique, but identical data.
The redundancy exists because each sheet is sorted based on different criteria.
I made a very simple macro which:
1. Selects the entire row of the active cell
2. Shades the row a specific color
VB:
[B]ActiveCell.EntireRow.Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 6299648 .TintAndShade = 0 .PatternTintAndShade = 0 End With[/B]If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I can't determine how to shade the identical rows of data in the other worksheets. There is one unique "identifier" cell within each row of data, under the column header "ID." However, the unique identifier cell is in a different column for the two sets of data (i.e. in one set of data, the "ID" code is in column X, and in the other set of data, the "ID" code is in column AA).
Is it possible to search based on the column title/header, which is "ID" since that column title/header is maintained in the two sets of data?
I thought the approach should be:
1. Select the row of the active cell
2. Ask excel to search the active workbook, and select all rows which contain data matching the cell data within the current active row under the heading "ID"
3. Shade all those rows
If the specific column must be specified, i.e. search "X" or search "AA" then I can make two macros for the discrete sets of data. However, I can't even figure out how to
Hi all, I have a macro which updates data sets and then save each individual data set to a designated folder. I need the macro to update the data sets, then remove the formulas attached to the figures in the sets before saving them to their specific folders... any ideas? Thanks in advance for any help
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 DartsClub. 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.
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 DartsClub. 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.
Hi,
I have two large data sets that I'm trying to combine (see attached exampleSheet1). 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?
Thanks.
I have two large data sets that I'm trying to combine (see attached exampleSheet1). 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?
Thanks.
Hi, I would like to know if there is a method of creating a graph with multiple sets of data for the Yaxis, and with different sets of data for the xaxis... 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
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
Hi there. I have two data sets. Each data set consists of a date and a corresponding numerical value. How do I plot both data sets to show the ACTUAL dates that the numbers were recorded on? I can plot the two series, however the dates of the data do not match the date on the xaxis. Hope I have not confused anyone!
thanks!
thanks!
Hi All,
Just wondering if someone could help me, I've got 2 sets of nearly identical data on one sheet,
Column B contains FirstName, Col C contains Surname and Col D again FirstName and Col E Surname.
I'm using the following formula to check that what's in Cols B and C match's with what's in Col D and E
=IF(AND(B1=C1,D1=E1),"","Error") which works fine. This will give me an error if the First and Last names are different.
The only problem is that one set of data might not have the information on the same Row as the other, I've tried using VLookup but couldn't really get it to work.
Please help
Thanks in advance
Just wondering if someone could help me, I've got 2 sets of nearly identical data on one sheet,
Column B contains FirstName, Col C contains Surname and Col D again FirstName and Col E Surname.
I'm using the following formula to check that what's in Cols B and C match's with what's in Col D and E
=IF(AND(B1=C1,D1=E1),"","Error") which works fine. This will give me an error if the First and Last names are different.
The only problem is that one set of data might not have the information on the same Row as the other, I've tried using VLookup but couldn't really get it to work.
Please help
Thanks in advance
I have three sets of tabs (sheets) in the same workbook. Each of these sets of three sheets work precisely the same way, except they have different data sources. I have a macro the performs data manipulations for one set of sheets. I want to use that same macro against each of the other two sets of sheets. I do not want to copy the macro two times then "replace" the sheet names. That will work, but then I must maintain three macros. Example: I want to run the macro with "IRB" as the sheet to be selected, rather than "UserMgt":
Sheets("UserMgt").Select
Is there a way to change "UserMgt" to a variable that can be changed each time the macro is called?
I have a set of data that can be divided into subsets. I've attached the sheet of data. There are 5 subsets and each of these has 5 subsets. The sets are defined by the Portfolio and End columns.
So, the 25 subsets look like:
10 INTL
20 INTL
30 INTL
40 INTL
50 INTL
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 subset 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 arrayformula 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.
Thanks,
Joe
So, the 25 subsets look like:
10 INTL
20 INTL
30 INTL
40 INTL
50 INTL
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 subset 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 arrayformula 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.
Thanks,
Joe
I have two very large data sets which I need to consolidate and I'm not sure about how to go about doing it. The two data sets have one matching column of data, which is FIPS or county codes which are four and five digit codes that correspond to various counties across the United States. My issue is that one of the data sets contains information on all 4000+ counties and the other has data on only about 800 so I need to combine the two sheets in a way that eliminates any counties from the larger data set that don't have a matching FIPS code in the smaller data set. Any help would be greatly appreciated. I'm using excel 2011. Attached is the workbook in case my description wasn't clear enough. Thanks.