Match (Fucntion, Script, or Macro)

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

Regards,
Saj

 

[Tom Ogilvy]

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.

 

[Guest]

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

Regards,
Sajid

 

[Guest]

I am having a difficulty using the code with my sreadsheet
which I sent you via email. I hope it was okay. Could
you please review and see if you can help me.

Regards,
Sajid