Finding data in a different sheet
 

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

Finding data in a different sheet
 

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