I have 2 "sets" of data in the same worksheet and would like to compare a 1
row by 2 column array in one data "set" to an n row X 2 column array in the
other data "set". I don't care about the result other than identifying any 1
X 2 arrays not in the n X 2 array.

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to compare a 1
row by 2 column array in one data "set" to an n row X 2 column array in
the
other data "set". I don't care about the result other than identifying
any 1
X 2 arrays not in the n X 2 array.

Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like to do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like "lookup",
but with a reference "range" instead of a reference "value".) Ideally any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I think I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to compare a 1
row by 2 column array in one data "set" to an n row X 2 column array in
the
other data "set". I don't care about the result other than identifying
any 1
X 2 arrays not in the n X 2 array.

Ok........

Based on the posted example what would the RESULTS be?

How many rows of data are there in each set? 100's? 1000's? Are they equal
in size?

Biff

"asaylor" wrote in message
...
Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like to do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then
search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like
"lookup",
but with a reference "range" instead of a reference "value".) Ideally any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I think
I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to compare
a 1
row by 2 column array in one data "set" to an n row X 2 column array in
the
other data "set". I don't care about the result other than identifying
any 1
X 2 arrays not in the n X 2 array.

based on the data sample 4006473 30000 4563 would be the only values returned
because they are in "Set 1" but not in "Set 2". It does not matter if a
string of data is in "Set 2" but not "Set 1"; it only matters if the string
is in "Set 1" and NOT "Set 2". The 2 sets of data have an unequal number of
entries and approximately 10,000 rows (all by 3 columns) per data set.

"Biff" wrote:

Ok........

Based on the posted example what would the RESULTS be?

How many rows of data are there in each set? 100's? 1000's? Are they equal
in size?

Biff

"asaylor" wrote in message
...
Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like to do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then
search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like
"lookup",
but with a reference "range" instead of a reference "value".) Ideally any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I think
I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to compare
a 1
row by 2 column array in one data "set" to an n row X 2 column array in
the
other data "set". I don't care about the result other than identifying
any 1
X 2 arrays not in the n X 2 array.

Ok........

Since there are 10k rows this would be the best way to do this:

Based on you posted sample:

Set 1 is in the range A1:C10

Set 2 is in the range A12:C21

Enter this formula in D1 and copy down to the end of set 1:

=IF(SUMPRODUCT(--(A$12:A$21=A1),--(B$12:B$21=B1),--(C$12:C$21=C1)),"",ROW())

To extract the desired values (if any):

Enter this formula in G1 and copy across to I1:

=IF(ROWS($1:1)<=COUNT($D$1:$D$10),INDEX(A$1:A$10,M ATCH(SMALL($D$1:$D$10,ROWS($1:1)),$D$1:$D$10,0))," ")

Select G1:I1 and copy down until you get blanks meaning no more matches.

Biff

"asaylor" wrote in message
...
based on the data sample 4006473 30000 4563 would be the only values
returned
because they are in "Set 1" but not in "Set 2". It does not matter if a
string of data is in "Set 2" but not "Set 1"; it only matters if the
string
is in "Set 1" and NOT "Set 2". The 2 sets of data have an unequal number
of
entries and approximately 10,000 rows (all by 3 columns) per data set.

"Biff" wrote:

Ok........

Based on the posted example what would the RESULTS be?

How many rows of data are there in each set? 100's? 1000's? Are they
equal
in size?

Biff

"asaylor" wrote in message
...
Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like to
do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then
search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like
"lookup",
but with a reference "range" instead of a reference "value".) Ideally
any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I
think
I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to
compare
a 1
row by 2 column array in one data "set" to an n row X 2 column array
in
the
other data "set". I don't care about the result other than
identifying
any 1
X 2 arrays not in the n X 2 array.

Biff,

Thank you very much for your help. The 2 functions work like a charm. I
had tried a couple variations of sumproduct, but got hung up thinking the
searches acted independently; anyway, thank you again.

"Biff" wrote:

Ok........

Since there are 10k rows this would be the best way to do this:

Based on you posted sample:

Set 1 is in the range A1:C10

Set 2 is in the range A12:C21

Enter this formula in D1 and copy down to the end of set 1:

=IF(SUMPRODUCT(--(A$12:A$21=A1),--(B$12:B$21=B1),--(C$12:C$21=C1)),"",ROW())

To extract the desired values (if any):

Enter this formula in G1 and copy across to I1:

=IF(ROWS($1:1)<=COUNT($D$1:$D$10),INDEX(A$1:A$10,M ATCH(SMALL($D$1:$D$10,ROWS($1:1)),$D$1:$D$10,0))," ")

Select G1:I1 and copy down until you get blanks meaning no more matches.

Biff

"asaylor" wrote in message
...
based on the data sample 4006473 30000 4563 would be the only values
returned
because they are in "Set 1" but not in "Set 2". It does not matter if a
string of data is in "Set 2" but not "Set 1"; it only matters if the
string
is in "Set 1" and NOT "Set 2". The 2 sets of data have an unequal number
of
entries and approximately 10,000 rows (all by 3 columns) per data set.

"Biff" wrote:

Ok........

Based on the posted example what would the RESULTS be?

How many rows of data are there in each set? 100's? 1000's? Are they
equal
in size?

Biff

"asaylor" wrote in message
...
Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like to
do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then
search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like
"lookup",
but with a reference "range" instead of a reference "value".) Ideally
any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I
think
I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to
compare
a 1
row by 2 column array in one data "set" to an n row X 2 column array
in
the
other data "set". I don't care about the result other than
identifying
any 1
X 2 arrays not in the n X 2 array.

You're welcome. Thanks for the feedback!

Biff

"asaylor" wrote in message
...
Biff,

Thank you very much for your help. The 2 functions work like a charm. I
had tried a couple variations of sumproduct, but got hung up thinking the
searches acted independently; anyway, thank you again.

"Biff" wrote:

Ok........

Since there are 10k rows this would be the best way to do this:

Based on you posted sample:

Set 1 is in the range A1:C10

Set 2 is in the range A12:C21

Enter this formula in D1 and copy down to the end of set 1:

=IF(SUMPRODUCT(--(A$12:A$21=A1),--(B$12:B$21=B1),--(C$12:C$21=C1)),"",ROW())

To extract the desired values (if any):

Enter this formula in G1 and copy across to I1:

=IF(ROWS($1:1)<=COUNT($D$1:$D$10),INDEX(A$1:A$10,M ATCH(SMALL($D$1:$D$10,ROWS($1:1)),$D$1:$D$10,0))," ")

Select G1:I1 and copy down until you get blanks meaning no more matches.

Biff

"asaylor" wrote in message
...
based on the data sample 4006473 30000 4563 would be the only values
returned
because they are in "Set 1" but not in "Set 2". It does not matter if
a
string of data is in "Set 2" but not "Set 1"; it only matters if the
string
is in "Set 1" and NOT "Set 2". The 2 sets of data have an unequal
number
of
entries and approximately 10,000 rows (all by 3 columns) per data set.

"Biff" wrote:

Ok........

Based on the posted example what would the RESULTS be?

How many rows of data are there in each set? 100's? 1000's? Are they
equal
in size?

Biff

"asaylor" wrote in message
...
Set 1
3986261 1800 5448
4006473 30000 4563
40000065 33200 2812
40000065 1000 2808
40000065 1997 2806
40000189 7814 6246
40000189 50000 6241
40000189 35000 6240
40000431 2500 5667
40000559 4015 4794

Set 2
999999 31570 0
3986261 1800 5448
40000065 33200 2812
40000065 1997 2806
40000065 1000 2808
40000189 7814 6246
40000189 35000 6240
40000189 50000 6241
40000431 2500 5667
40000559 4015 4794

The data in "Set 1" and "Set 2" are in 3 columns. What I would like
to
do
is search for the 1st row in "Set 1" throughout all of "Set 2"; then
search
the 2nd row in "Set 1" throughout "Set 2", etc. . . (Kinda like
"lookup",
but with a reference "range" instead of a reference "value".)
Ideally
any
ranges in "Set 1" but NOT in "Set 2" could output to a new worksheet
or
different cells to eliminate additional sort, copy, and paste steps.

As an alternative, If I could merge the data from 3 columns to 1, I
think
I
could use the lookup function.

Let me know if this clarifies my question.

Thanks



"Biff" wrote:

Define "compare".

Be more specific. Provide an example and the desired result.

Biff

"asaylor" wrote in message
...
I have 2 "sets" of data in the same worksheet and would like to
compare
a 1
row by 2 column array in one data "set" to an n row X 2 column
array
in
the
other data "set". I don't care about the result other than
identifying
any 1
X 2 arrays not in the n X 2 array.