Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Matching Date From Two Groups
I have two data sets that I am trying to compa
Each set has three columns: A=Order Number, B=Customer, C=Amount. I am trying to get a formula to compare the sets populated into column D to show whether the Order appears in the other data set Data Set 1 Col A Col B Col C Col D Order Cust Amt In Data Set 2 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Dear 20 (Desired anser - N or False) Data Set 2 Col A Col B Col C Col D Order Cust Amt In Data Set 1 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Charlie 20 (Desired anser - N or False) I know I've seen similar posts, but I just have not been able to grasp it. Any help is greatly appreciated. |
#2
|
|||
|
|||
Hi!
Assume Data Set 1 is on sheet1 and Data Set 2 is on sheet2. For DS1: =IF(SUMPRODUCT(--(Sheet2!B$2:B$4&Sheet2!C$2:C$4=B2&C2)),"Y","N") Copy down as needed. For DS2 just change the sheet references to Sheet1. Biff "wsteel" wrote in message ... I have two data sets that I am trying to compa Each set has three columns: A=Order Number, B=Customer, C=Amount. I am trying to get a formula to compare the sets populated into column D to show whether the Order appears in the other data set Data Set 1 Col A Col B Col C Col D Order Cust Amt In Data Set 2 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Dear 20 (Desired anser - N or False) Data Set 2 Col A Col B Col C Col D Order Cust Amt In Data Set 1 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Charlie 20 (Desired anser - N or False) I know I've seen similar posts, but I just have not been able to grasp it. Any help is greatly appreciated. |
#3
|
|||
|
|||
Biff, thanks.
I do have the data on two sheets. It looks like your formula would work if the two data sets had the same number of rows. Unfortunately they don't. Data set 1 has a couple hundred more rows than data set two. This prevents me from sorting them and having the rows line up. Here's a little more specific information on the actual files. Each row is refers to a sales order and contains sales order number, customer name, and dollar amount. I get data dumps from two corporate databases which don't play well together, hence the descrepancies. Data source #1 has about 600 rows. Source #2 has about 500 rows. What I'm ultimately trying to identify is which rows appear in both (these represent the reconciled orders) and which rows appear in one data set or the other (the ones I then need to take action on). I hope this explains the situation better. The first column in each data set is actually a sales order number. I'm "Biff" wrote: Hi! Assume Data Set 1 is on sheet1 and Data Set 2 is on sheet2. For DS1: =IF(SUMPRODUCT(--(Sheet2!B$2:B$4&Sheet2!C$2:C$4=B2&C2)),"Y","N") Copy down as needed. For DS2 just change the sheet references to Sheet1. Biff "wsteel" wrote in message ... I have two data sets that I am trying to compa Each set has three columns: A=Order Number, B=Customer, C=Amount. I am trying to get a formula to compare the sets populated into column D to show whether the Order appears in the other data set Data Set 1 Col A Col B Col C Col D Order Cust Amt In Data Set 2 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Dear 20 (Desired anser - N or False) Data Set 2 Col A Col B Col C Col D Order Cust Amt In Data Set 1 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Charlie 20 (Desired anser - N or False) I know I've seen similar posts, but I just have not been able to grasp it. Any help is greatly appreciated. |
#4
|
|||
|
|||
Hi!
It shouldn't matter that one data set is larger or smaller than the other. The formula is not comparing one entire column in DS1 to one entire column in DS2. It's only comparing 2 concatenated cells against 2 concatenated columns. As long as columns B and C have the same amount rows it should work. Columns B and C in DS1 can be 500 rows and columns B and C of DS2 can be 600 rows. You'd just have to change the range size in the formula for the appropriate data set. Biff "wsteel" wrote in message ... Biff, thanks. I do have the data on two sheets. It looks like your formula would work if the two data sets had the same number of rows. Unfortunately they don't. Data set 1 has a couple hundred more rows than data set two. This prevents me from sorting them and having the rows line up. Here's a little more specific information on the actual files. Each row is refers to a sales order and contains sales order number, customer name, and dollar amount. I get data dumps from two corporate databases which don't play well together, hence the descrepancies. Data source #1 has about 600 rows. Source #2 has about 500 rows. What I'm ultimately trying to identify is which rows appear in both (these represent the reconciled orders) and which rows appear in one data set or the other (the ones I then need to take action on). I hope this explains the situation better. The first column in each data set is actually a sales order number. I'm "Biff" wrote: Hi! Assume Data Set 1 is on sheet1 and Data Set 2 is on sheet2. For DS1: =IF(SUMPRODUCT(--(Sheet2!B$2:B$4&Sheet2!C$2:C$4=B2&C2)),"Y","N") Copy down as needed. For DS2 just change the sheet references to Sheet1. Biff "wsteel" wrote in message ... I have two data sets that I am trying to compa Each set has three columns: A=Order Number, B=Customer, C=Amount. I am trying to get a formula to compare the sets populated into column D to show whether the Order appears in the other data set Data Set 1 Col A Col B Col C Col D Order Cust Amt In Data Set 2 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Dear 20 (Desired anser - N or False) Data Set 2 Col A Col B Col C Col D Order Cust Amt In Data Set 1 1 Acme 10 (Desired answer - Y, or True) 2 Bear 15 (Desired answer - Y, or True) 3 Charlie 20 (Desired anser - N or False) I know I've seen similar posts, but I just have not been able to grasp it. Any help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Count items between specific hours on a matching date | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |