script/macro that can compare data of two columns

[Saj]

I am trying to find a script and/or macro that can
compare data of two columns in two different worksheets:
A and B. 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
1 Apple
2 Orange
3 Mango
6 Pear

Worksheet B:
Column A Column B
Apple
Mango
Orange
Mango
Apple
Strawbery
Orange
Apple
Kiwi
Pear

I want to compare values of column B of Worksheet A with
the Column B of Worksheet B. (Please note that there is
one-to-many relationship between Worksheets A to B)
Where there is a match, I want to put a corresponding
value from the column A of Workseet A into the column A
of worksheet B. So after the comparison, the worksheet B
should look like this.

Worksheet B
Column A Column B
1 Apple
3 Mango
2 Orange
3 Mango
1 Apple
Strawbery
2 Orange
1 Apple
Kiwi
6 Pear

Can anyone help me please providing me any script and/or
macro that can do this?

 

[Tom Ogilvy]

=Index(WorksheetsA!$A$1:$A$100,Match(worksheetsB!B1,worksheetsA!$B$1:$B$100,
0),1)

in A1 of WorksheetsB and then drag fill down the column.

if you don't want a N/A# result for rows that don't match you can do

=if(ierror(Match(worksheetsB!B1,worksheetsA!$B$1:$B$100,0)),"",Index(Workshe
etsA!$A$1:$A$100,Match(worksheetsB!B1,worksheetsA!$B$1:$B$100,0),1))

 

[Guest]

The firs solution works, but the secondone gives does not
work. I get "#name?" in all the column A of Worksheet B

 

[Tom Ogilvy]

ierror should be iserror (typo)

=IF(ISERROR(MATCH(WorksheetsB!B1,WorksheetsA!$B$1:$B$100,0)),"",INDEX(Worksh
eetsA!$A$1:$A$100,MATCH(WorksheetsB!B1,WorksheetsA!$B$1:$B$100,0),1))

 

[Guest]

Thank you very much...This works, but my requirements get
complex as the tbales have Many-to-Many relationships.
Here is revised requirements; I truly appreciate your
cooperation:

I am trying to find a script and/or macro that can
compare data of two columns in two different worksheets:
A and B. 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
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