Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
I hope I can explain this clearly enough. And I thank you in advance for any
suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
A sum product formula should work for this if I understand the question
correctly. Take a look at this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Robin" wrote: I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
assuming the column headers are these
"benefit_name" "benefit_amount_name" "benefit_amount_method" This should get you started. Sub findStuff() Dim v As Variant, v1 As Variant Dim i As Long, k As Long, sAddr As String Dim res As Variant, bMatch As Boolean Dim rng() As Range, rng1 As Range Dim rng2 As Range v = Array( _ "benefit_name", _ "benefit_amount_name", _ "benefit_amount_method") v1 = Array( _ "specifcName", _ "specificAmountName", _ "specific_method") ReDim rng(LBound(v) To UBound(v)) For i = LBound(v) To UBound(v) res = Application.Match(v(i), Rows(1).Cells, 0) Set rng(i) = Range("A1").Offset(0, res - 1) rng(i).Interior.ColorIndex = 5 Next Set rng1 = Columns(rng(LBound(rng)).Column).Cells Debug.Print rng1.Address Set rng2 = rng1.Find(v1(LBound(v1)), rng(LBound(rng))(1)) If Not rng2 Is Nothing Then sAddr = rng2.Address Do bMatch = True rng2.Interior.ColorIndex = 6 For k = LBound(v) + 1 To UBound(v) If Cells(rng2.Row, rng(k).Column).Value < v1(k) Then Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 7 bMatch = False Exit For Else Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 6 End If Next If bMatch Then Worksheets("ABC").Range("B9").Value _ = Cells(rng2.Row, 21).Value Exit Sub End If Set rng2 = rng1.FindNext(rng2) Loop While rng2.Address < sAddr End If End Sub -- Regards, Tom Ogilvy "Robin" wrote in message ... I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
Doesn't work yet, but I'll play with it (tomorrow) and let you know if I need
further assistance. I must say, Tom, I regularly use your posts in this forum. You have been more help to me in the past couple of months than you know. Thank you! "Tom Ogilvy" wrote: assuming the column headers are these "benefit_name" "benefit_amount_name" "benefit_amount_method" This should get you started. Sub findStuff() Dim v As Variant, v1 As Variant Dim i As Long, k As Long, sAddr As String Dim res As Variant, bMatch As Boolean Dim rng() As Range, rng1 As Range Dim rng2 As Range v = Array( _ "benefit_name", _ "benefit_amount_name", _ "benefit_amount_method") v1 = Array( _ "specifcName", _ "specificAmountName", _ "specific_method") ReDim rng(LBound(v) To UBound(v)) For i = LBound(v) To UBound(v) res = Application.Match(v(i), Rows(1).Cells, 0) Set rng(i) = Range("A1").Offset(0, res - 1) rng(i).Interior.ColorIndex = 5 Next Set rng1 = Columns(rng(LBound(rng)).Column).Cells Debug.Print rng1.Address Set rng2 = rng1.Find(v1(LBound(v1)), rng(LBound(rng))(1)) If Not rng2 Is Nothing Then sAddr = rng2.Address Do bMatch = True rng2.Interior.ColorIndex = 6 For k = LBound(v) + 1 To UBound(v) If Cells(rng2.Row, rng(k).Column).Value < v1(k) Then Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 7 bMatch = False Exit For Else Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 6 End If Next If bMatch Then Worksheets("ABC").Range("B9").Value _ = Cells(rng2.Row, 21).Value Exit Sub End If Set rng2 = rng1.FindNext(rng2) Loop While rng2.Address < sAddr End If End Sub -- Regards, Tom Ogilvy "Robin" wrote in message ... I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
Well, it worked for me so I don't know what to say. Perhaps I didn't
understand your description. -- Regards, Tom Ogilvy "Robin" wrote in message ... Doesn't work yet, but I'll play with it (tomorrow) and let you know if I need further assistance. I must say, Tom, I regularly use your posts in this forum. You have been more help to me in the past couple of months than you know. Thank you! "Tom Ogilvy" wrote: assuming the column headers are these "benefit_name" "benefit_amount_name" "benefit_amount_method" This should get you started. Sub findStuff() Dim v As Variant, v1 As Variant Dim i As Long, k As Long, sAddr As String Dim res As Variant, bMatch As Boolean Dim rng() As Range, rng1 As Range Dim rng2 As Range v = Array( _ "benefit_name", _ "benefit_amount_name", _ "benefit_amount_method") v1 = Array( _ "specifcName", _ "specificAmountName", _ "specific_method") ReDim rng(LBound(v) To UBound(v)) For i = LBound(v) To UBound(v) res = Application.Match(v(i), Rows(1).Cells, 0) Set rng(i) = Range("A1").Offset(0, res - 1) rng(i).Interior.ColorIndex = 5 Next Set rng1 = Columns(rng(LBound(rng)).Column).Cells Debug.Print rng1.Address Set rng2 = rng1.Find(v1(LBound(v1)), rng(LBound(rng))(1)) If Not rng2 Is Nothing Then sAddr = rng2.Address Do bMatch = True rng2.Interior.ColorIndex = 6 For k = LBound(v) + 1 To UBound(v) If Cells(rng2.Row, rng(k).Column).Value < v1(k) Then Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 7 bMatch = False Exit For Else Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 6 End If Next If bMatch Then Worksheets("ABC").Range("B9").Value _ = Cells(rng2.Row, 21).Value Exit Sub End If Set rng2 = rng1.FindNext(rng2) Loop While rng2.Address < sAddr End If End Sub -- Regards, Tom Ogilvy "Robin" wrote in message ... I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
Any problems I ran into were my own fault. I had an error in one of the
values to look for.... I tweaked the code to find the value I really wanted, and it worked like a charm. You have amazed me once again. "Tom Ogilvy" wrote: Well, it worked for me so I don't know what to say. Perhaps I didn't understand your description. -- Regards, Tom Ogilvy "Robin" wrote in message ... Doesn't work yet, but I'll play with it (tomorrow) and let you know if I need further assistance. I must say, Tom, I regularly use your posts in this forum. You have been more help to me in the past couple of months than you know. Thank you! "Tom Ogilvy" wrote: assuming the column headers are these "benefit_name" "benefit_amount_name" "benefit_amount_method" This should get you started. Sub findStuff() Dim v As Variant, v1 As Variant Dim i As Long, k As Long, sAddr As String Dim res As Variant, bMatch As Boolean Dim rng() As Range, rng1 As Range Dim rng2 As Range v = Array( _ "benefit_name", _ "benefit_amount_name", _ "benefit_amount_method") v1 = Array( _ "specifcName", _ "specificAmountName", _ "specific_method") ReDim rng(LBound(v) To UBound(v)) For i = LBound(v) To UBound(v) res = Application.Match(v(i), Rows(1).Cells, 0) Set rng(i) = Range("A1").Offset(0, res - 1) rng(i).Interior.ColorIndex = 5 Next Set rng1 = Columns(rng(LBound(rng)).Column).Cells Debug.Print rng1.Address Set rng2 = rng1.Find(v1(LBound(v1)), rng(LBound(rng))(1)) If Not rng2 Is Nothing Then sAddr = rng2.Address Do bMatch = True rng2.Interior.ColorIndex = 6 For k = LBound(v) + 1 To UBound(v) If Cells(rng2.Row, rng(k).Column).Value < v1(k) Then Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 7 bMatch = False Exit For Else Cells(rng2.Row, rng(k).Column).Interior.ColorIndex = 6 End If Next If bMatch Then Worksheets("ABC").Range("B9").Value _ = Cells(rng2.Row, 21).Value Exit Sub End If Set rng2 = rng1.FindNext(rng2) Loop While rng2.Address < sAddr End If End Sub -- Regards, Tom Ogilvy "Robin" wrote in message ... I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching multiple columns
Thank you for your response. The sumproduct won't work for my particular
situation, but the link provided some really interesting reading - wow! I appreciate your thoughts. :-) "Jim Thomlinson" wrote: A sum product formula should work for this if I understand the question correctly. Take a look at this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Robin" wrote: I hope I can explain this clearly enough. And I thank you in advance for any suggestions or thoughts on this. I have 3 columns of data (locations are dynamic, but the column headers will always be as shown below) I want to search the first column for a specific benefit, then search the second column using those rows that had the value from the first search. I want to search the second column for a match, then search the third column (based on results from the previous matches) and find a final value in a fourth column. Example: "benefit_name" "benefit_amount_name" "benefit_amount_method" Long Term Care SSAB Long Term Disability Capital Accumulation Account CAA Salary Percent SSAB Vision Capital Accumulation Account Salary Deferral Fixed Amount SSAB Long Term Disability Capital Accumulation Account CAA Remaining Allowance Pct. I want to find the Capital Accumulation Account benefit where the benefit_amount_name = CAA and the benefit_amount_method = Remaining Allowance Pct. If I could do this in VBA, that would be slick. I want this to be unseen by the end user. I just want to grab the value I'm looking for and plop it into a cell. Is there a way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for multiple strings and return multiple solutions | Excel Worksheet Functions | |||
Searching rows with multiple columns criteria | Excel Worksheet Functions | |||
Searching the row that contains the same two columns | Excel Programming | |||
Searching the row that contains the same two columns | Excel Programming | |||
Tips on searching and comparing multiple columns on separate sheets. | Excel Programming |