formular to check if details in one cell match any details in another cell!

[tonylpcs@yahoo.co.uk]

Hi Everyone,

Ive been trying to work this out but keep geting stuck, hope someone can help me.

I have a spreadsheet and colum A hold a list of clients names, with each row holding a different name, I need a formular or code that can doe the following.

Look at the name in colum A lets say cell A1, and see if it matches any of the names in colum C on any row, if it does then i need it to put a unique number in colum b next to the name it looked at i.e. if it checking A1 then the number goes in B1, then put the same number in the colum D next to the cell in colum C it matched! i.e. if A1 and C200 have the same name in it the B1 will have a number in it e.g. "1" and D200 will have the same number in it e.g "1".

now i also need it to reconise if the D cell aready has a number in it and if so use that number.

again for example there might be 4 rows with the same name in them, well i need the all to have the same number!

can anyone help please?

Thanks in advance

Tony

 

[milesUK]

This might be what you are after. It assumes that the names in column A are unique.

Excel Workbook
	A	B	C	D
1	bob	1	bob	1
2	jim	2	jim	2
3	sam	3	sam	3
4	paul	no match	bob	1
5			jim	2
6			sam	3
7			bob	1
8			jim	2
9			sam	3
10			bob	1
11			jim	2
12			sam	3
13			pete	no match
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=IF(NOT(ISNA(VLOOKUP(A1,C:C,1,FALSE))),ROW(),"---")
D1	=IF(NOT(ISNA(VLOOKUP(C1,A:B,2,FALSE))),VLOOKUP(C1,A:B,2,FALSE),"no match")

 

[tonylpcs@yahoo.co.uk]

thank you,

just what i needed.

Tony