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?

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?

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?

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?

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?

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?

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?