Searching and Matching Lists
 

Pls help with a macro to solve the task below:

Sheet1, column A has an unfixed or undetermined range of names
starting at "A2".
Sheet2 column A has a related name list also.
For every item in sheet2 column A, search through sheet1 Column A
Where a match is found, indicate "PRESENT" in the corresponding cell
in Sheet2 column B.

Searching and Matching Lists
 

In Sheet2!B2, enter:
=IF(COUNTIF(Sheet1!A:A,A2)0,"PRESENT","")
then drag down

HTH
--
AP

"sylink" a écrit dans le message de news:
...
Pls help with a macro to solve the task below:

Sheet1, column A has an unfixed or undetermined range of names
starting at "A2".
Sheet2 column A has a related name list also.
For every item in sheet2 column A, search through sheet1 Column A
Where a match is found, indicate "PRESENT" in the corresponding cell
in Sheet2 column B.

Searching and Matching Lists
 

Sub CompareLists()
Dim sh1 as Worksheet, sh2 as Worksheet
Dim rng1 as Range, rng2 as Range
Dim cell as Range, res as Variant
set sh1 = Worksheets("sheet1")
set sh2 = Worksheets("sheet2")
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown ))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown ))
for each cell in rng2
res = Application.Match(cell.Value,rng1,0)
if not iserror(res) then
cell.offset(0,1).Value = "Present"
end if
next
end sub

--
Regards,
Tom Ogilvy


"sylink" wrote:

Pls help with a macro to solve the task below:

Sheet1, column A has an unfixed or undetermined range of names
starting at "A2".
Sheet2 column A has a related name list also.
For every item in sheet2 column A, search through sheet1 Column A
Where a match is found, indicate "PRESENT" in the corresponding cell
in Sheet2 column B.

Searching and Matching Lists
 

Thanks for the prompt response. I'll ur solutions a try