Thank you-let me apply to my data and see if it works. I
appreciate all your help.

Regards,
Sajid

-----Original Message-----
Sub TesterAAA()
Dim rngAA As Range, rngAB As Range, rngBB As Range
Dim rngCB As Range, rng As Range
Dim s2 As String, sAddr As String, res As Variant
With Worksheets("WorksheetsA")
Set rngAA = .Range(.Cells(1, 1), .Cells(1, 1).End
(xlDown))
Set rngAB = rngAA.Offset(0, 1)
End With
With Worksheets("WorksheetsB")
Set rngBB = .Range(.Cells(1, 2), .Cells(1, 2).End
(xlDown))
End With
With Worksheets("WorksheetsC")
Set rngCB = .Range(.Cells(2, 2), .Cells(2, 2).End
(xlDown))
End With

For Each cell In rngBB
Set rng = rngAB.Find(cell.Value)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
res = Application.Match(rng.Offset(0, 1), rngCB, 0)
If Not IsError(res) Then
s2 = rngCB(res).Offset(0, -1)
Else
s2 = rng.Offset(0, 1)
End If

If cell.Offset(0, 1).Value = s2 Then
' match found, get data
cell.Offset(0, -1).Value = rng.Offset(0, -
1).Value
Exit Do
End If
Set rng = rngAB.FindNext(rng)
Loop Until rng.Address = sAddr
End If
Next


End Sub


Seemed to work.
--
Regards,
Tom Ogilvy


wrote in message
...
Thank you for your response and help. I believe the
requirements are final. Could you please help.

Regards,
Saj



-----Original Message-----
This is your third revision of the requirement. when
you finally figure out
what the requirement is, feel free to post back.

--
Regards,
Tom Ogilvy


wrote in message
...
I am trying to find a script and/or macro that can
compare data of two columns in three different
worksheets:
A, B, and C. Also put corresponding data of Column A
from
worksheet A to the column A of Worksheet B.
Mentioned
below is a sample:

Worksheet A:
Column A Column B Column C
1 Apple Small
2 Orange Small
3 Orange Large
4 Mango Small
5 Pear Medium
6 Orange 2 inch
7 Orange 5 ounce

Worksheet B:
Column A Column B Column C
Apple Small
Mango Small
Orange Small
Mango Small
Apple Small
Orange Sizes
Orange Measurements
Apple Small
Kiwi Small
Pear Small

Worksheet C:
Column A Column B
Sizes 2 inch
weight 5 ounce

I want to compare values of column B of Worksheet A
with
the Column B of Worksheet B. (Please note that
there is
Many-to-many relationships between Worksheets A to
B)
Where there is a match, I want to put a
corresponding
value from the column A of Worksheet A into the
column
A
of worksheet B. However, since the relationship is
many-
to-many, I have to compare the values in the column
C
from Wkst A to Column C in the Wkst B for
differentiation. In some cases, nstead of value,
the
alias is mentioned in the Column C of wkst B. For
such
cases, I have to match the value of Column C from
wkst
A
to the Column B of the Wkst c. So after the
comparison,
the worksheet B should look like this.

Worksheet B
Column A Column B Column C
1 Apple Small
4 Mango Small
2 Orange Small
4 Mango Small
1 Apple Small
6 Orange Sizes
7 Orange Weight
1 Apple Small
Kiwi Small
5 Pear Small



.



.