How To Match Two Sets Of Data Between Two Worksheets


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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.
Similar Excel Video Tutorials
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,
i m facing some problem in excel data workbook 2007, Actually as in the attachment there are many data sets, and the sum of Column E of every data set is shown in yellow. Thus i need only those data sets to be included in my sheet where sum is > 500 (above 500) . Rest of the data sets where sum is less than 500 should be deleted from there.
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 am trying to do a simple Excel calculation.
I am trying an IF with nested IF statements but I have to hard code the numbers and then it is not to infinity.
We sell an item in groups of 4.
When a cell contains the number that the customer needs then I want to know how many 'groups' of 4 they need to order.
Example
Customer Needs 12
A1 = 12/4
Answer in C3 = 3 sets of 4 needed to be ordered.

Customer needs 13
A1 = 13/4
Answer in C3 = 4 sets of 4 needed to be ordered.

0/4 = 0 sets needed
1/4 = 1 sets needed
2/4 = 1 sets needed
3/4 = 1 sets needed
4/4 = 1 sets needed
5/4 = 2 sets needed
6/4 = 2 sets needed
7/4 = 2 sets needed
8/4 = 2 sets needed
9/4 = 3 sets needed
10/4 = 3 sets needed
11/4 = 3 sets needed
12/4 = 3 sets needed
13/4 = 4 sets needed
14/4 = 4 sets needed
15/4 = 4 sets needed
16/4 = 4 sets needed
17/4 = 5 sets needed
18/4 = 5 sets needed
19/4 = 5 sets needed
20/4 = 5 sets needed
21/4 = 6 sets needed
22/4 = 6 sets needed
23/4 = 6 sets needed
24/4 = 6 sets needed
so on down .......
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:
Hi,
i m facing some problem in excel data workbook 2007, Actually as in the attachment there are many data sets, and the sum of each data set is shown in yellow. Thus i need only those data sets to be included in my sheet where sum is >500 (below 500) and rest of the data sets should'nt be there.........
I've got two different worksheets that I would like to combine data from. In one worksheet, I've got about 40 sets of data that are grouped together (A1, A2, A3, A4, etc.).
I'd like to be able to copy specific data sets to another worksheet using some sort of lookup feature or something like that. For example, is there any way that I can type the information of A1 or lookup the information in cell A1 of the other sheet and have A2, A3, A4, A5, etc. of that sheet copied into my destination sheet?
Hopefully, I explained this well enough. Thanks, in advance, for your help.
I have a need to find matching pairs across multiple sets of data. Typically I have up to 2000 locations that I want to match based on up to 200 sets of data returned by date. I want to find correllations between the locations and ideally match or pair any one location with up to 4 others.
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
Hi
I am new to this kindly bear with me.I want to match a certain data between two worksheets. The excel sheet is attached for your reference.I want the data to be organized as per sheet3 in the workbook. Kindly help me out. Thanking you in advance.
Hi,
i m facing some problem in excel data workbook 2007, Actually as in the attachment there are many data sets, and the sum of Column E of every data set is shown in yellow. Thus i need only those data sets to be included in my sheet where sum is > 500 (above 500) . Rest of the data sets where sum is less than 500 should be deleted from there.
1089  1259 171 171
1347  1412 66 66
1499  1579 81 81
1670  1750 81 81
SUM 399
13827  14108 282 282
14497  14583 87 87
14653  14766 114 114
14864  15064 201 201
15169  15291 123 123
15420  15659 240 240
SUM 1047
376  480 105 105
709  819 111 111
1088  1240 153 153
SUM 369
2109  2261 153 153
2313  2618 306 306
SUM 459
5313  5501 189 189
SUM 189
1920  3002 1083 1083
SUM 1083
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
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
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.
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.
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 currently have 3 different sets of data on 1 sheet directly below each other.
I am wanting to filter each of these sets of data on different critirea. is
this possible or will I have to put each sets of data on seperate sheets?
regards
I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.
EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500
Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)
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:
[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 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.
Hi,
I would like to write some code which will allow me to display each worksheet (9 in total) in a workbook for a set time (perhaps 10 minutes per worksheet) and when finished, iterate the process again indefinitely. I have some code which refreshes the data in each worksheet already and so would like to incorporate both sets of code into a single macro.
The end result will be 9 sets of continuously updated data which will be displayed on a large screen, to be used for process monitoring.
Any help would be greatly appreciated.
Eire.
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.
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
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!
Dear all,
As an excel newbie, I was wondering if you could help me with the following problem. I want to match two sets of data and if there is a match I would like to paste the subsequent row.
I added an example in an attachment of the data structure
Note that the list in colum C, D and E is larger than colum A
Any help would be really great.
Thanking you in advance,
elduder