Thread: 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.

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

HTH
--
AP

"sylink" <xtianlaw@yahoo.com> a écrit dans le message de news:
1147792362.162905.58990@i39g2000cwa.googlegroups.com...
> 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.
>

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

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