Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you want to try:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If

MultiCatIf = myStr

End Function


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,"",750000,C1:C4,", ",FALSE)

This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)

1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates

(I thought it would be nice to use the same code to allow/prohibit duplicates.)

You may want to try:

=multicatif(A1:A4,"",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.

====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.

If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.



Brian wrote:

Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

--

Dave Peterson

ps. There is a bug in this--but I bet it won't affect you.

If you have a short string in one description (say Brian) and it occurs in
another (already included string) like:

Jim, Brian, Ralph

Then the separate singleton Brian won't be included in the concatenation.

If you're using xl2k or higher, you can use this UDF instead:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean
Dim myDelim As String

myDelim = Chr(1)

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, myDelim & myStr & myDelim, _
myDelim & ConCatVal & myDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & myDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(myDelim) + 1)
'replace that chr(1) with the real delimiter
'replace requires xl2k or higher
myStr = Replace(myStr, myDelim, sDelim)
End If

MultiCatIf = myStr

End Function

Everything else stays the same.

Dave Peterson wrote:

I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you want to try:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If

MultiCatIf = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,"",750000,C1:C4,", ",FALSE)

This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)

1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates

(I thought it would be nice to use the same code to allow/prohibit duplicates.)

You may want to try:

=multicatif(A1:A4,"",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.

====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.

If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.

Brian wrote:

Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

--

Dave Peterson

--

Dave Peterson

Thanks Dave....
Unfortunately I am not familiar with how to incorporate what you have posted
below in Excel.
I'm not well versed in Macros other than very basic ones...so I don't even
know where to begin with the answer you have posted below.
Can I trouble you to be a little more specific as to how I can use the
information you provided?

"Dave Peterson" wrote:

ps. There is a bug in this--but I bet it won't affect you.

If you have a short string in one description (say Brian) and it occurs in
another (already included string) like:

Jim, Brian, Ralph

Then the separate singleton Brian won't be included in the concatenation.

If you're using xl2k or higher, you can use this UDF instead:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean
Dim myDelim As String

myDelim = Chr(1)

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, myDelim & myStr & myDelim, _
myDelim & ConCatVal & myDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & myDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(myDelim) + 1)
'replace that chr(1) with the real delimiter
'replace requires xl2k or higher
myStr = Replace(myStr, myDelim, sDelim)
End If

MultiCatIf = myStr

End Function

Everything else stays the same.

Dave Peterson wrote:

I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you want to try:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If

MultiCatIf = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,"",750000,C1:C4,", ",FALSE)

This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)

1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates

(I thought it would be nice to use the same code to allow/prohibit duplicates.)

You may want to try:

=multicatif(A1:A4,"",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.

====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.

If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.

Brian wrote:

Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

--

Dave Peterson

--

Dave Peterson
.

Did you look at the links that I suggested?

Did you have trouble following the "short course" instructions?



Brian wrote:

Thanks Dave....
Unfortunately I am not familiar with how to incorporate what you have posted
below in Excel.
I'm not well versed in Macros other than very basic ones...so I don't even
know where to begin with the answer you have posted below.
Can I trouble you to be a little more specific as to how I can use the
information you provided?

"Dave Peterson" wrote:

ps. There is a bug in this--but I bet it won't affect you.

If you have a short string in one description (say Brian) and it occurs in
another (already included string) like:

Jim, Brian, Ralph

Then the separate singleton Brian won't be included in the concatenation.

If you're using xl2k or higher, you can use this UDF instead:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean
Dim myDelim As String

myDelim = Chr(1)

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, myDelim & myStr & myDelim, _
myDelim & ConCatVal & myDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & myDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(myDelim) + 1)
'replace that chr(1) with the real delimiter
'replace requires xl2k or higher
myStr = Replace(myStr, myDelim, sDelim)
End If

MultiCatIf = myStr

End Function

Everything else stays the same.

Dave Peterson wrote:

I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you want to try:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If

MultiCatIf = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,"",750000,C1:C4,", ",FALSE)

This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)

1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates

(I thought it would be nice to use the same code to allow/prohibit duplicates.)

You may want to try:

=multicatif(A1:A4,"",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.

====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.

If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.

Brian wrote:

Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

--

Dave Peterson

--

Dave Peterson
.


--

Dave Peterson

sorry...I only saw the one at the top...didn't realize you posted some to the
bottom as well.
Thanks

"Dave Peterson" wrote:

Did you look at the links that I suggested?

Did you have trouble following the "short course" instructions?



Brian wrote:

Thanks Dave....
Unfortunately I am not familiar with how to incorporate what you have posted
below in Excel.
I'm not well versed in Macros other than very basic ones...so I don't even
know where to begin with the answer you have posted below.
Can I trouble you to be a little more specific as to how I can use the
information you provided?

"Dave Peterson" wrote:

ps. There is a bug in this--but I bet it won't affect you.

If you have a short string in one description (say Brian) and it occurs in
another (already included string) like:

Jim, Brian, Ralph

Then the separate singleton Brian won't be included in the concatenation.

If you're using xl2k or higher, you can use this UDF instead:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean
Dim myDelim As String

myDelim = Chr(1)

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, myDelim & myStr & myDelim, _
myDelim & ConCatVal & myDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & myDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(myDelim) + 1)
'replace that chr(1) with the real delimiter
'replace requires xl2k or higher
myStr = Replace(myStr, myDelim, sDelim)
End If

MultiCatIf = myStr

End Function

Everything else stays the same.

Dave Peterson wrote:

I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you want to try:

Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant

Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean

If CritRng.Columns.Count < 1 _
Or ConCatRng.Columns.Count < 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If CritRng.Rows.Count < ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If

If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If

myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count

CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If

myExpression = CritVal & myOperator & myVal

'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)

If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text

KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) 0 Then
KeepThisVal = False
End If
End If

If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr

If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If

MultiCatIf = myStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,"",750000,C1:C4,", ",FALSE)

This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)

1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates

(I thought it would be nice to use the same code to allow/prohibit duplicates.)

You may want to try:

=multicatif(A1:A4,"",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.

====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.

If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.

Brian wrote:

Is there a formula that will allow you to search for amount criteria and list
the words that meet that criteria?

For example, I have the following data in columns A, B & C:

$1,000,000 5% Prime Fund
$3,000,000 1% Treasury Bill
$500,000 4% Money Market Fund
$2,000,000 5% Prime Fund

I'd like to possibly write a concatenate formula that will tell me what
investments have a balance greater than $750,000
Ideally I'd like the answer in the cell to be "Prime Fund, Treasury Bill"
I realize I can't use VLookup because it only finds the first answer that
meets the criteria....and I also want to prevent the formula for giving two
of the same answers (using the example above, I don't want the answer to be
"Prime Fund, Treasury Bill, Prime Fund"

Is this possible using some Excel fomulas?

--

Dave Peterson

--