Compare data on 2 separate worksheets.
 

I am trying to figure out how I can compare data on 2 different worksheets.
Here is what I am trying to do...

I have Worksheet 1 (WS1) and Worksheet 2 (WS2). Each worksheet has over 5000
rows. I need to compare the data in WS1-column B and WS2-column B. If there
are any entries that match, I need the data that is in WS2-column A to be
copied and pasted into WS1-column A.

Is there an easy way to do this?

Your help is appreciated.

-Wayne

assuming not more than one match per item
in column A sheet 1
=if(iserror(match(B1,Sheet2!B:B,0),"",Offset(Sheet 2!A1,match(B1,Sheet2!B:B,0)-1,0)

"Wayne" wrote:

I am trying to figure out how I can compare data on 2 different worksheets.
Here is what I am trying to do...

I have Worksheet 1 (WS1) and Worksheet 2 (WS2). Each worksheet has over 5000
rows. I need to compare the data in WS1-column B and WS2-column B. If there
are any entries that match, I need the data that is in WS2-column A to be
copied and pasted into WS1-column A.

Is there an easy way to do this?

Your help is appreciated.

-Wayne

On Wed, 6 Jul 2005 13:10:03 -0700, "Wayne"
wrote:

I am trying to figure out how I can compare data on 2 different worksheets.
Here is what I am trying to do...

I have Worksheet 1 (WS1) and Worksheet 2 (WS2). Each worksheet has over 5000
rows. I need to compare the data in WS1-column B and WS2-column B. If there
are any entries that match, I need the data that is in WS2-column A to be
copied and pasted into WS1-column A.

Is there an easy way to do this?

Your help is appreciated.

-Wayne


Wayne,

Here is a "bulldog" way to do it:

In column "C" (or anywhere convenient) put the formula
=if(ws1!b1=ws2!b1,ws2!a1," ") Then sort on column "C" so all the
results are in one contiguous set of rows. From there, you can copy
Column "C" to Column "A" (copy/paste values).

Good Luck,

Don S

Hi,

Here is another solution though not the best one. In cell A1 in WS1, array
enter (Ctrl+Shift+Enter) the following formula

IF(OR(EXACT(A1,WS2!$A$1:A500)),A1,"")

The reason i say this is not the best solution is that there will be blanks
in rows where there is no match.

Hope this of some help

"Wayne" wrote:

I am trying to figure out how I can compare data on 2 different worksheets.
Here is what I am trying to do...

I have Worksheet 1 (WS1) and Worksheet 2 (WS2). Each worksheet has over 5000
rows. I need to compare the data in WS1-column B and WS2-column B. If there
are any entries that match, I need the data that is in WS2-column A to be
copied and pasted into WS1-column A.

Is there an easy way to do this?

Your help is appreciated.

-Wayne