Comparing two lists

  • Thread starter Thread starter Kyle
  • Start date Start date
K

Kyle

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
 
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)),"",VLOOKUP(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 said:
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".
Click to expand...

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
 
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.
 
Back
Top