script/macro that can compare data of two columns
 

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?

script/macro that can compare data of two columns
 

=Index(WorksheetsA!$A$1:$A$100,Match(worksheetsB!B 1,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))

--
Regards,
Tom Ogilvy

"Saj" wrote in message
...
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?

script/macro that can compare data of two columns
 

The firs solution works, but the secondone gives does not
work. I get "#name?" in all the column A of Worksheet B
-----Original Message-----
=Index(WorksheetsA!$A$1:$A$100,Match(worksheets B!
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))

--
Regards,
Tom Ogilvy

"Saj" wrote in
message
...
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?




.

script/macro that can compare data of two columns
 

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))

--
Regards,
Tom Ogilvy

wrote in message
...
The firs solution works, but the secondone gives does not
work. I get "#name?" in all the column A of Worksheet B
-----Original Message-----
=Index(WorksheetsA!$A$1:$A$100,Match(worksheets B!
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))

--
Regards,
Tom Ogilvy

"Saj" wrote in
message
...
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?




.

script/macro that can compare data of two columns
 

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










-----Original Message-----
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,Worksheets A!
$B$1:$B$100,0),1))

--
Regards,
Tom Ogilvy

wrote in message
...
The firs solution works, but the secondone gives does
not
work. I get "#name?" in all the column A of Worksheet
B
-----Original Message-----
=Index(WorksheetsA!$A$1:$A$100,Match(worksheets B!
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))

--
Regards,
Tom Ogilvy

"Saj" wrote in
message
...
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?




.



.