Searching multiple columns

[Robin]

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?

[Jim Thomlinson]

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?

[Tom Ogilvy]

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" <Robin@discussions.microsoft.com> wrote in message
news:67E68C30-E169-4232-B204-D2DE832F55B2@microsoft.com...
> 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?

[Robin]

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" <Robin@discussions.microsoft.com> wrote in message
> news:67E68C30-E169-4232-B204-D2DE832F55B2@microsoft.com...
> > 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?
>
>
>

[Tom Ogilvy]

Well, it worked for me so I don't know what to say. Perhaps I didn't
understand your description.

--
Regards,
Tom Ogilvy

"Robin" <Robin@discussions.microsoft.com> wrote in message
news:C9597811-8030-4423-9199-9B0A6414E46F@microsoft.com...
> 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" <Robin@discussions.microsoft.com> wrote in message
> > news:67E68C30-E169-4232-B204-D2DE832F55B2@microsoft.com...
> > > 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?
> >
> >
> >

[Robin]

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" <Robin@discussions.microsoft.com> wrote in message
> news:C9597811-8030-4423-9199-9B0A6414E46F@microsoft.com...
> > 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" <Robin@discussions.microsoft.com> wrote in message
> > > news:67E68C30-E169-4232-B204-D2DE832F55B2@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>

[Robin]

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?