Matching Date From Two Groups

I have two data sets that I am trying to compare:

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.

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" <[email protected]> wrote in message
news:[email protected]...
>I have two data sets that I am trying to compare:
>
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >I have two data sets that I am trying to compare:
> >
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have two data sets that I am trying to compare:
>> >
>> > 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.
>> >
>>
>>
>>