Finding data in a different sheet

[trem]

Hi there, i have 2 sheets, say named a and b, sheet a contains data extracted of the internet. Is it possible for me to program sheet b to search for any data which is already typed out in sheet b in column b which is in sheet a and if it is not present to say it is so. The data in sheets a is not organised in any particular column and that is what is giving me problems so far.

This program also needs to be shared with others so i cannot do it manually unfortunatly.

Thanks for any help.

[Toppers]

Try this:

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rnga As Range, rngb As Range
Set ws1 = Worksheets("sheet1") ' <=== Sheet a
Set ws2 = Worksheets("sheet2") ' <=== sheet b
With ws1
Set rnga = .Range("a1:a" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With ws2
Set rngb = .Range("b1:b" & .Cells(Rows.Count, "b").End(xlUp).Row)
For Each cell In rngb
If Application.CountIf(rnga, cell) = 0 Then
cell.Offset(0, 1) = "Not found" '<=== column C
End If
Next
End With


Or

use a formula (in column C of sheet b); place in C1 and copy down. Change
ranges/sheet names as required :

=IF(COUNTIF(Sheet1!$A$1:$A$100,Sheet2!B1)=0,"Not Present","Present")

HTH

"trem" wrote:

>
> Hi there, i have 2 sheets, say named a and b, sheet a contains data
> extracted of the internet. Is it possible for me to program sheet b to
> search for any data which is already typed out in sheet b in column b
> which is in sheet a and if it is not present to say it is so. The data
> in sheets a is not organised in any particular column and that is what
> is giving me problems so far.
>
> This program also needs to be shared with others so i cannot do it
> manually unfortunatly.
>
> Thanks for any help.
>
>
> --
> trem
> ------------------------------------------------------------------------
> trem's Profile: http://www.excelforum.com/member.php...o&userid=32222
> View this thread: http://www.excelforum.com/showthread...hreadid=519707
>
>