comparing ranges/arrays

[asaylor]

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

"asaylor" <[email protected]> wrote in message
news:[email protected]...
>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.

[asaylor]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

[asaylor]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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]

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

"asaylor" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>

[asaylor]

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,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
>
> "asaylor" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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]

You're welcome. Thanks for the feedback!

Biff

"asaylor" <[email protected]> wrote in message
news:[email protected]...
> 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,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
>>
>> "asaylor" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>