| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match (Fucntion, Script, or Macro)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match (Fucntion, Script, or Macro)
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match (Fucntion, Script, or Macro)
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match (Fucntion, Script, or Macro)
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match (Fucntion, Script, or Macro)
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match (Fucntion, Script, or Macro)
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 -----Original Message----- 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 . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Script help | Excel Worksheet Functions | |||
| VB script/macro help - please !! | Excel Discussion (Misc queries) | |||
| Macro or script | Excel Programming | |||
| Macro or Script??? | Excel Programming | |||
Linear Mode
