| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If, Or function or macro to compare 3 columns of numbers | Excel Worksheet Functions | |||
| Importing Data - Macro or Script | Excel Worksheet Functions | |||
| How do I create a macro that will compare columns and place data | Excel Worksheet Functions | |||
| Macro to compare two columns of data | Excel Discussion (Misc queries) | |||
| Macro to compare two columns of data | Excel Discussion (Misc queries) | |||
Linear Mode
