Comparing two lists
 

I have two lists in a workbook, each on there own sheet. I want to find if
the value of on cell in list "A" has an exact match in list "B". Also how
can I strip all characters preceding the character "/" in a cell and also
remove the space that is right after the character "/"?

For the easy part:
To delete the characters to the left of the "/" and the following blank,
select the cells, then use Tools - Text to Columns...

specifiy both the "/" and the blank as delimiters. You will end up with two
columns neatly split by "/ ". Throw away the part do don't need.

The other part requires more than a Student
--
Gary's Student


"Kyle" wrote:

I have two lists in a workbook, each on there own sheet. I want to find if
the value of on cell in list "A" has an exact match in list "B". Also how
can I strip all characters preceding the character "/" in a cell and also
remove the space that is right after the character "/"?

Assuming your lists are both in column A, one on sheet1 and the other on
sheet2........in B1 of sheet1 put this formula and copy down, to find those
that match on sheet2

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"",VLOOKU P(A1,Sheet2!A:A,1,FALSE))

To strip the numbers preceeding the / and the space following it,........put
this in C1 and copy down

=MID(A1,FIND("/",A1,1)+2,99)

Vaya con Dios,
Chuck, CABGx3



"Kyle" wrote in message
...
I have two lists in a workbook, each on there own sheet. I want to find
if
the value of on cell in list "A" has an exact match in list "B". Also how
can I strip all characters preceding the character "/" in a cell and also
remove the space that is right after the character "/"?

"Kyle" wrote
I have two lists in a workbook, each on there own sheet.
I want to find if the value of on cell in list "A"
has an exact match in list "B".

Just another way to try ..

Assuming list "A" is in Sheet1 col A, A1 down,
and list "B" is is in Sheet2 col A, A1 down

In Sheet1
-------------
You could put in B1:
=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Y","")
and copy B1 down until the last row of data in col A

Col B will flag a "Y" for data in col A
matching those in Sheet2's col A

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks to all of you. I needed away to make sure the memo field in my PO
list crossed referenced to an invoice. Hate to work so hard and forget to
invoice a customer.


"Kyle" wrote in message
...
I have two lists in a workbook, each on there own sheet. I want to find if
the value of on cell in list "A" has an exact match in list "B". Also how
can I strip all characters preceding the character "/" in a cell and also
remove the space that is right after the character "/"?

You're welcome !
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kyle" wrote in message
...
Thanks to all of you. I needed away to make sure the memo field in my PO
list crossed referenced to an invoice. Hate to work so hard and forget to
invoice a customer.