Compare ranges different size
 

Hello everybody,

While checking excel newsgroups, i've found that it's quite easy to compare
two ranges that have the same size (say A1:A10 against B1:B10).

In my particular case however, it would be much better if I could compare
ranges that are different in size (let's say A1:C15 against D1:E7), possibly
containing some blank cells as well. From these two ranges, I would like to
extract all the (non-blank) cells that appear in A1:C15 but not in D1:E7.

Do you have any idea what is the best way to do this?
Are there any useful formulas that could cover my case or am I obliged to
pass through VBA?

Thanks a lot for any hints!
Cheers,

Mark

You would have to write a VBA routine. You need to incorporate the logic as to
what to do with a blank cell: the equivalent of deleting it and Shift-Left or
Shift-Up.

On Tue, 22 Feb 2005 16:16:30 +0100, "markx"
wrote:

Hello everybody,

While checking excel newsgroups, i've found that it's quite easy to compare
two ranges that have the same size (say A1:A10 against B1:B10).

In my particular case however, it would be much better if I could compare
ranges that are different in size (let's say A1:C15 against D1:E7), possibly
containing some blank cells as well. From these two ranges, I would like to
extract all the (non-blank) cells that appear in A1:C15 but not in D1:E7.

Do you have any idea what is the best way to do this?
Are there any useful formulas that could cover my case or am I obliged to
pass through VBA?

Thanks a lot for any hints!
Cheers,

Mark

And then you have the problem of what do if there are not the same number of
filled cells in the two ranges.

On Tue, 22 Feb 2005 16:16:30 +0100, "markx"
wrote:

Hello everybody,

While checking excel newsgroups, i've found that it's quite easy to compare
two ranges that have the same size (say A1:A10 against B1:B10).

In my particular case however, it would be much better if I could compare
ranges that are different in size (let's say A1:C15 against D1:E7), possibly
containing some blank cells as well. From these two ranges, I would like to
extract all the (non-blank) cells that appear in A1:C15 but not in D1:E7.

Do you have any idea what is the best way to do this?
Are there any useful formulas that could cover my case or am I obliged to
pass through VBA?

Thanks a lot for any hints!
Cheers,

Mark

Thanks a lot Myrna!

I will try to elaborate the adequate routine, although I cannot see at the
moment what is the additional difficulty with the different number of cells
in two ranges...
But I will find it!

Cheers,
Mark




"Myrna Larson" wrote in message
...
And then you have the problem of what do if there are not the same number
of
filled cells in the two ranges.

On Tue, 22 Feb 2005 16:16:30 +0100, "markx"

wrote:

Hello everybody,

While checking excel newsgroups, i've found that it's quite easy to
compare
two ranges that have the same size (say A1:A10 against B1:B10).

In my particular case however, it would be much better if I could compare
ranges that are different in size (let's say A1:C15 against D1:E7),
possibly
containing some blank cells as well. From these two ranges, I would like
to
extract all the (non-blank) cells that appear in A1:C15 but not in D1:E7.

Do you have any idea what is the best way to do this?
Are there any useful formulas that could cover my case or am I obliged to
pass through VBA?

Thanks a lot for any hints!
Cheers,

Mark