comparing ranges/arrays

[Guest]

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]

Define "compare".

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

Biff

 

[Guest]

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]

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

 

[Guest]

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]

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,MATCH(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

 

[Guest]

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]

You're welcome. Thanks for the feedback!

Biff

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.