New Function: ConcatenateIF

I previously posted a question looking for a Concatenate function similar to
SumIF. This is my solution:

Public Function ConcatenateIF(Lookup_Value_Range As Range, _
Match_Range As Range, _
Concatenate_Range As Range) As
String

Dim x As Long
Dim Lookup_Value As String
Dim Source_Cell As Range
Dim Lookup_Row_Count As Long

Set Source_Cell = Application.Caller
Lookup_Value = Lookup_Value_Range _
.Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value

ConcatenateIF = ""

If Lookup_Value <> 0 Then
Lookup_Row_Count = Match_Range.Rows.Count
For x = 1 To Lookup_Row_Count
If Lookup_Value = Match_Range.Cells(x, 1).Value _
And Concatenate_Range.Cells(x, 1).Value <> 0 Then
If ConcatenateIF = "" Then
ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
Else
ConcatenateIF = ConcatenateIF & Chr(10) &
Concatenate_Range.Cells(x, 1).Value
End If
End If
Next x
End If

End Function

-----------------------------------------

Thanks

Simon Shaw
www.simontools.com

Hi Simon

i actually couldn't get this to work ... maybe i'm using the wrong info in
the wrong parameters
A1:A10 contains the range i want to check for the criteria
G10 contains the criteria
K1:K10 contains the range i want to concatenate

so i set
Lookup_Value_Range to A1:A10
Match_Range to G10
Concatenate_Range to K1:K10

and got a #VALUE as a result.

I tested it with both text and numbers in A1:A10 and G10
what am i doing wrong?

Cheers
JulieD



"Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
news:DF88E438-33F1-4F10-965D-2F0274C24E18@microsoft.com...
>I previously posted a question looking for a Concatenate function similar
>to
> SumIF. This is my solution:
>
> Public Function ConcatenateIF(Lookup_Value_Range As Range, _
> Match_Range As Range, _
> Concatenate_Range As Range) As
> String
>
> Dim x As Long
> Dim Lookup_Value As String
> Dim Source_Cell As Range
> Dim Lookup_Row_Count As Long
>
> Set Source_Cell = Application.Caller
> Lookup_Value = Lookup_Value_Range _
> .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value
>
> ConcatenateIF = ""
>
> If Lookup_Value <> 0 Then
> Lookup_Row_Count = Match_Range.Rows.Count
> For x = 1 To Lookup_Row_Count
> If Lookup_Value = Match_Range.Cells(x, 1).Value _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) &
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
>
> End Function
>
> -----------------------------------------
>
> Thanks
>
> Simon Shaw
> www.simontools.com
>
>
>

Thanks Julie, I have improved the variable names to make it more clear and
changed the order of the variables to match SUMIF

------------------------------------
Public Function ConcatenateIF(Match_Range As Range, _
Criteria_Range As Range, _
Concatenate_Range As Range) As
String

Dim x As Long
Dim Criteria_Value As String
Dim Source_Cell As Range
Dim Match_Row_Count As Long

Set Source_Cell = Application.Caller
If Criteria_Range.Rows.Count > 1 Then
Criteria_Value = Criteria_Range _
.Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Value
Else
Criteria_Value = Criteria_Range.Value
End If

ConcatenateIF = ""

If Criteria_Value <> 0 Then
Match_Row_Count = Match_Range.Rows.Count
For x = 1 To Match_Row_Count
If Criteria_Value = Match_Range.Cells(x, 1).Value _
And Concatenate_Range.Cells(x, 1).Value <> 0 Then
If ConcatenateIF = "" Then
ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
Else
ConcatenateIF = ConcatenateIF & Chr(10) & _
Concatenate_Range.Cells(x, 1).Value
End If
End If
Next x
End If

End Function
---------------------------------------

Simon


"JulieD" wrote:

> Hi Simon
>
> i actually couldn't get this to work ... maybe i'm using the wrong info in
> the wrong parameters
> A1:A10 contains the range i want to check for the criteria
> G10 contains the criteria
> K1:K10 contains the range i want to concatenate
>
> so i set
> Lookup_Value_Range to A1:A10
> Match_Range to G10
> Concatenate_Range to K1:K10
>
> and got a #VALUE as a result.
>
> I tested it with both text and numbers in A1:A10 and G10
> what am i doing wrong?
>
> Cheers
> JulieD
>
>
>
> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
> news:DF88E438-33F1-4F10-965D-2F0274C24E18@microsoft.com...
> >I previously posted a question looking for a Concatenate function similar
> >to
> > SumIF. This is my solution:
> >
> > Public Function ConcatenateIF(Lookup_Value_Range As Range, _
> > Match_Range As Range, _
> > Concatenate_Range As Range) As
> > String
> >
> > Dim x As Long
> > Dim Lookup_Value As String
> > Dim Source_Cell As Range
> > Dim Lookup_Row_Count As Long
> >
> > Set Source_Cell = Application.Caller
> > Lookup_Value = Lookup_Value_Range _
> > .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value
> >
> > ConcatenateIF = ""
> >
> > If Lookup_Value <> 0 Then
> > Lookup_Row_Count = Match_Range.Rows.Count
> > For x = 1 To Lookup_Row_Count
> > If Lookup_Value = Match_Range.Cells(x, 1).Value _
> > And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> > If ConcatenateIF = "" Then
> > ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
> > Else
> > ConcatenateIF = ConcatenateIF & Chr(10) &
> > Concatenate_Range.Cells(x, 1).Value
> > End If
> > End If
> > Next x
> > End If
> >
> > End Function
> >
> > -----------------------------------------
> >
> > Thanks
> >
> > Simon Shaw
> > www.simontools.com
> >
> >
> >
>
>
>

Hi Simon

that seems to work well with numerics in the Match and Criteria .. .but not
text .. BTW i've posted by CONCAT_IF function to your other post in .misc
you might like to have a look at it.

Cheers
JulieD

"Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
news:9F3CA4CD-C671-4C2B-B594-40D18158A65C@microsoft.com...
> Thanks Julie, I have improved the variable names to make it more clear and
> changed the order of the variables to match SUMIF
>
> ------------------------------------
> Public Function ConcatenateIF(Match_Range As Range, _
> Criteria_Range As Range, _
> Concatenate_Range As Range) As
> String
>
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
>
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Value
> Else
> Criteria_Value = Criteria_Range.Value
> End If
>
> ConcatenateIF = ""
>
> If Criteria_Value <> 0 Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Value _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
>
> End Function
> ---------------------------------------
>
> Simon
>
>
> "JulieD" wrote:
>
>> Hi Simon
>>
>> i actually couldn't get this to work ... maybe i'm using the wrong info
>> in
>> the wrong parameters
>> A1:A10 contains the range i want to check for the criteria
>> G10 contains the criteria
>> K1:K10 contains the range i want to concatenate
>>
>> so i set
>> Lookup_Value_Range to A1:A10
>> Match_Range to G10
>> Concatenate_Range to K1:K10
>>
>> and got a #VALUE as a result.
>>
>> I tested it with both text and numbers in A1:A10 and G10
>> what am i doing wrong?
>>
>> Cheers
>> JulieD
>>
>>
>>
>> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
>> news:DF88E438-33F1-4F10-965D-2F0274C24E18@microsoft.com...
>> >I previously posted a question looking for a Concatenate function
>> >similar
>> >to
>> > SumIF. This is my solution:
>> >
>> > Public Function ConcatenateIF(Lookup_Value_Range As Range, _
>> > Match_Range As Range, _
>> > Concatenate_Range As Range)
>> > As
>> > String
>> >
>> > Dim x As Long
>> > Dim Lookup_Value As String
>> > Dim Source_Cell As Range
>> > Dim Lookup_Row_Count As Long
>> >
>> > Set Source_Cell = Application.Caller
>> > Lookup_Value = Lookup_Value_Range _
>> > .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value
>> >
>> > ConcatenateIF = ""
>> >
>> > If Lookup_Value <> 0 Then
>> > Lookup_Row_Count = Match_Range.Rows.Count
>> > For x = 1 To Lookup_Row_Count
>> > If Lookup_Value = Match_Range.Cells(x, 1).Value _
>> > And Concatenate_Range.Cells(x, 1).Value <> 0 Then
>> > If ConcatenateIF = "" Then
>> > ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
>> > Else
>> > ConcatenateIF = ConcatenateIF & Chr(10) &
>> > Concatenate_Range.Cells(x, 1).Value
>> > End If
>> > End If
>> > Next x
>> > End If
>> >
>> > End Function
>> >
>> > -----------------------------------------
>> >
>> > Thanks
>> >
>> > Simon Shaw
>> > www.simontools.com
>> >
>> >
>> >
>>
>>
>>

More Improvements: Thanks JulieD
__________________________________________

Public Function ConcatenateIF(Match_Range As Range, _
Criteria_Range As Range, _
Concatenate_Range As Range) As
String

' created by Simon Shaw
' Match_Range - Range to match the criteria against
' Criteria_Range - Range to get the criteria to match against the Match_Range
' if range is more than one cell it will pull the value
' from the same row as the application.caller
' Concatenate_Range - Range to concatenate text from
' Match_Range and Concatenate_Range must be the same size

Dim x As Long
Dim Criteria_Value As String
Dim Source_Cell As Range
Dim Match_Row_Count As Long

If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
Exit Function
End If

Set Source_Cell = Application.Caller
If Criteria_Range.Rows.Count > 1 Then
Criteria_Value = Criteria_Range _
.Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
Else
Criteria_Value = Criteria_Range.Text
End If

ConcatenateIF = ""

If Criteria_Value <> "" Then
Match_Row_Count = Match_Range.Rows.Count
For x = 1 To Match_Row_Count
If Criteria_Value = Match_Range.Cells(x, 1).Text _
And Concatenate_Range.Cells(x, 1).Value <> 0 Then
If ConcatenateIF = "" Then
ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
Else
ConcatenateIF = ConcatenateIF & Chr(10) & _
Concatenate_Range.Cells(x, 1).Value
End If
End If
Next x
End If

End Function
_________________________________________________


"Simon Shaw" wrote:

> Thanks Julie, I have improved the variable names to make it more clear and
> changed the order of the variables to match SUMIF
>
> ------------------------------------
> Public Function ConcatenateIF(Match_Range As Range, _
> Criteria_Range As Range, _
> Concatenate_Range As Range) As
> String
>
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
>
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Value
> Else
> Criteria_Value = Criteria_Range.Value
> End If
>
> ConcatenateIF = ""
>
> If Criteria_Value <> 0 Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Value _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
>
> End Function
> ---------------------------------------
>
> Simon
>
>
> "JulieD" wrote:
>
> > Hi Simon
> >
> > i actually couldn't get this to work ... maybe i'm using the wrong info in
> > the wrong parameters
> > A1:A10 contains the range i want to check for the criteria
> > G10 contains the criteria
> > K1:K10 contains the range i want to concatenate
> >
> > so i set
> > Lookup_Value_Range to A1:A10
> > Match_Range to G10
> > Concatenate_Range to K1:K10
> >
> > and got a #VALUE as a result.
> >
> > I tested it with both text and numbers in A1:A10 and G10
> > what am i doing wrong?
> >
> > Cheers
> > JulieD
> >
> >
> >
> > "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
> > news:DF88E438-33F1-4F10-965D-2F0274C24E18@microsoft.com...
> > >I previously posted a question looking for a Concatenate function similar
> > >to
> > > SumIF. This is my solution:
> > >
> > > Public Function ConcatenateIF(Lookup_Value_Range As Range, _
> > > Match_Range As Range, _
> > > Concatenate_Range As Range) As
> > > String
> > >
> > > Dim x As Long
> > > Dim Lookup_Value As String
> > > Dim Source_Cell As Range
> > > Dim Lookup_Row_Count As Long
> > >
> > > Set Source_Cell = Application.Caller
> > > Lookup_Value = Lookup_Value_Range _
> > > .Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value
> > >
> > > ConcatenateIF = ""
> > >
> > > If Lookup_Value <> 0 Then
> > > Lookup_Row_Count = Match_Range.Rows.Count
> > > For x = 1 To Lookup_Row_Count
> > > If Lookup_Value = Match_Range.Cells(x, 1).Value _
> > > And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> > > If ConcatenateIF = "" Then
> > > ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
> > > Else
> > > ConcatenateIF = ConcatenateIF & Chr(10) &
> > > Concatenate_Range.Cells(x, 1).Value
> > > End If
> > > End If
> > > Next x
> > > End If
> > >
> > > End Function
> > >
> > > -----------------------------------------
> > >
> > > Thanks
> > >
> > > Simon Shaw
> > > www.simontools.com
> > >
> > >
> > >
> >
> >
> >

Interesting, can you supply example?

my email address is:

simon AT simonstools.com

I will reply with a sample spreadsheet.


"Jim at Eagle" wrote:

> Interesting, can you supply example?

Simon,

Your ConcatenateIF Function is just what I was looking for, but I can't
get it to work.

My scenario =

A B C
1 T T Al
2 T Henry
3 F Joe
4 F Mike
5 T Jim


My desired result =

AlHenryJim

My Formula=

=ConcatenateIF(B1:B5,"="&A1,C1:C5)

My actual result =

#Name?

Your Function Code=

Public Function ConcatenateIF(Match_Range As Range, Criteria_Range As
Range, _
Concatenate_Range As Range) As String
' created by Simon Shaw
' Match_Range - Range to match the criteria against
' Criteria_Range - Range to get the criteria to match against the
Match_Range
' if range is more than one cell it will pull the value
' from the same row as the application.caller
' Concatenate_Range - Range to concatenate text from
' Match_Range and Concatenate_Range must be the same size
Dim x As Long
Dim Criteria_Value As String
Dim Source_Cell As Range
Dim Match_Row_Count As Long
If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
Exit Function
End If
Set Source_Cell = Application.Caller
If Criteria_Range.Rows.Count > 1 Then
Criteria_Value = Criteria_Range _
.Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
Else
Criteria_Value = Criteria_Range.Text
End If
ConcatenateIF = ""
If Criteria_Value <> "" Then
Match_Row_Count = Match_Range.Rows.Count
For x = 1 To Match_Row_Count
If Criteria_Value = Match_Range.Cells(x, 1).Text _
And Concatenate_Range.Cells(x, 1).Value <> 0 Then
If ConcatenateIF = "" Then
ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
Else
ConcatenateIF = ConcatenateIF & Chr(10) & _
Concatenate_Range.Cells(x, 1).Value
End If
End If
Next x
End If
End Function

What am I missing?

Thanks,
Jim


Simon Shaw wrote:
> More Improvements: Thanks JulieD
> __________________________________________
>
> Public Function ConcatenateIF(Match_Range As Range, _
> Criteria_Range As Range,
_
> Concatenate_Range As
Range) As
> String
>
> ' created by Simon Shaw
> ' Match_Range - Range to match the criteria against
> ' Criteria_Range - Range to get the criteria to match against the
Match_Range
> ' if range is more than one cell it will pull the value
> ' from the same row as the application.caller
> ' Concatenate_Range - Range to concatenate text from
> ' Match_Range and Concatenate_Range must be the same size
>
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
>
> If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> Exit Function
> End If
>
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> Else
> Criteria_Value = Criteria_Range.Text
> End If
>
> ConcatenateIF = ""
>
> If Criteria_Value <> "" Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Text _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
>
> End Function
> _________________________________________________
>
>

when using the formula, remove the "="& so it reads:

=ConcatenateIF(B1:B5,A1,C1:C5)

"jim.excel@gmail.com" wrote:

> Simon,
>
> Your ConcatenateIF Function is just what I was looking for, but I can't
> get it to work.
>
> My scenario =
>
> A B C
> 1 T T Al
> 2 T Henry
> 3 F Joe
> 4 F Mike
> 5 T Jim
>
>
> My desired result =
>
> AlHenryJim
>
> My Formula=
>
> =ConcatenateIF(B1:B5,"="&A1,C1:C5)
>
> My actual result =
>
> #Name?
>
> Your Function Code=
>
> Public Function ConcatenateIF(Match_Range As Range, Criteria_Range As
> Range, _
> Concatenate_Range As Range) As String
> ' created by Simon Shaw
> ' Match_Range - Range to match the criteria against
> ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
> ' if range is more than one cell it will pull the value
> ' from the same row as the application.caller
> ' Concatenate_Range - Range to concatenate text from
> ' Match_Range and Concatenate_Range must be the same size
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
> If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> Exit Function
> End If
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> Else
> Criteria_Value = Criteria_Range.Text
> End If
> ConcatenateIF = ""
> If Criteria_Value <> "" Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Text _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
> End Function
>
> What am I missing?
>
> Thanks,
> Jim
>
>
> Simon Shaw wrote:
> > More Improvements: Thanks JulieD
> > __________________________________________
> >
> > Public Function ConcatenateIF(Match_Range As Range, _
> > Criteria_Range As Range,
> _
> > Concatenate_Range As
> Range) As
> > String
> >
> > ' created by Simon Shaw
> > ' Match_Range - Range to match the criteria against
> > ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
> > ' if range is more than one cell it will pull the value
> > ' from the same row as the application.caller
> > ' Concatenate_Range - Range to concatenate text from
> > ' Match_Range and Concatenate_Range must be the same size
> >
> > Dim x As Long
> > Dim Criteria_Value As String
> > Dim Source_Cell As Range
> > Dim Match_Row_Count As Long
> >
> > If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> > Exit Function
> > End If
> >
> > Set Source_Cell = Application.Caller
> > If Criteria_Range.Rows.Count > 1 Then
> > Criteria_Value = Criteria_Range _
> > .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> > Else
> > Criteria_Value = Criteria_Range.Text
> > End If
> >
> > ConcatenateIF = ""
> >
> > If Criteria_Value <> "" Then
> > Match_Row_Count = Match_Range.Rows.Count
> > For x = 1 To Match_Row_Count
> > If Criteria_Value = Match_Range.Cells(x, 1).Text _
> > And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> > If ConcatenateIF = "" Then
> > ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> > Else
> > ConcatenateIF = ConcatenateIF & Chr(10) & _
> > Concatenate_Range.Cells(x, 1).Value
> > End If
> > End If
> > Next x
> > End If
> >
> > End Function
> > _________________________________________________
> >
> >
>
>

in my code you will want to change the
& Chr(10) &
to just
&

"jim.excel@gmail.com" wrote:

> Simon,
>
> Your ConcatenateIF Function is just what I was looking for, but I can't
> get it to work.
>
> My scenario =
>
> A B C
> 1 T T Al
> 2 T Henry
> 3 F Joe
> 4 F Mike
> 5 T Jim
>
>
> My desired result =
>
> AlHenryJim
>
> My Formula=
>
> =ConcatenateIF(B1:B5,"="&A1,C1:C5)
>
> My actual result =
>
> #Name?
>
> Your Function Code=
>
> Public Function ConcatenateIF(Match_Range As Range, Criteria_Range As
> Range, _
> Concatenate_Range As Range) As String
> ' created by Simon Shaw
> ' Match_Range - Range to match the criteria against
> ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
> ' if range is more than one cell it will pull the value
> ' from the same row as the application.caller
> ' Concatenate_Range - Range to concatenate text from
> ' Match_Range and Concatenate_Range must be the same size
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
> If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> Exit Function
> End If
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> Else
> Criteria_Value = Criteria_Range.Text
> End If
> ConcatenateIF = ""
> If Criteria_Value <> "" Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Text _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
> End Function
>
> What am I missing?
>
> Thanks,
> Jim
>
>
> Simon Shaw wrote:
> > More Improvements: Thanks JulieD
> > __________________________________________
> >
> > Public Function ConcatenateIF(Match_Range As Range, _
> > Criteria_Range As Range,
> _
> > Concatenate_Range As
> Range) As
> > String
> >
> > ' created by Simon Shaw
> > ' Match_Range - Range to match the criteria against
> > ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
> > ' if range is more than one cell it will pull the value
> > ' from the same row as the application.caller
> > ' Concatenate_Range - Range to concatenate text from
> > ' Match_Range and Concatenate_Range must be the same size
> >
> > Dim x As Long
> > Dim Criteria_Value As String
> > Dim Source_Cell As Range
> > Dim Match_Row_Count As Long
> >
> > If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> > Exit Function
> > End If
> >
> > Set Source_Cell = Application.Caller
> > If Criteria_Range.Rows.Count > 1 Then
> > Criteria_Value = Criteria_Range _
> > .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> > Else
> > Criteria_Value = Criteria_Range.Text
> > End If
> >
> > ConcatenateIF = ""
> >
> > If Criteria_Value <> "" Then
> > Match_Row_Count = Match_Range.Rows.Count
> > For x = 1 To Match_Row_Count
> > If Criteria_Value = Match_Range.Cells(x, 1).Text _
> > And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> > If ConcatenateIF = "" Then
> > ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> > Else
> > ConcatenateIF = ConcatenateIF & Chr(10) & _
> > Concatenate_Range.Cells(x, 1).Value
> > End If
> > End If
> > Next x
> > End If
> >
> > End Function
> > _________________________________________________
> >
> >
>
>

Hi Jim

(Simon sorry to butt in here)

you might like to try a Concatenate IF fuction that Harald Staff and I
cobbled together ....
---------
Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String
'written by JulieD and Harald Staff
'ConcCheck - range to check for the criteria
'ConcCrit - the criteria
'ConcRange - range to concatenation
'DelimitWith - the delimination character(s)

Dim Cel As Range
Dim i As Long, j As Long
Dim checkarray() As String
Dim rangearray() As String

If ConcRange Is Nothing Then Set ConcRange = ConcCheck

i = ConcCheck.Count
j = ConcRange.Count

If i <> j Then
Exit Function
End If

ReDim checkarray(i - 1)
ReDim rangearray(i - 1)

i = 0
For Each Cel In ConcCheck
checkarray(i) = Cel.Text
i = i + 1
Next
i = 0
For Each Cel In ConcRange
rangearray(i) = Cel.Text
i = i + 1
Next

For i = 0 To j - 1
If checkarray(i) = ConcCrit Then CONCAT_IF = _
CONCAT_IF & rangearray(i) & DelimitWith
Next
If CONCAT_IF <> "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith))
End Function
-------

---------
Let us know how you go

Cheers
JulieD

<jim.excel@gmail.com> wrote in message
news:1112127162.975948.183320@g14g2000cwa.googlegroups.com...
> Simon,
>
> Your ConcatenateIF Function is just what I was looking for, but I can't
> get it to work.
>
> My scenario =
>
> A B C
> 1 T T Al
> 2 T Henry
> 3 F Joe
> 4 F Mike
> 5 T Jim
>
>
> My desired result =
>
> AlHenryJim
>
> My Formula=
>
> =ConcatenateIF(B1:B5,"="&A1,C1:C5)
>
> My actual result =
>
> #Name?
>
> Your Function Code=
>
> Public Function ConcatenateIF(Match_Range As Range, Criteria_Range As
> Range, _
> Concatenate_Range As Range) As String
> ' created by Simon Shaw
> ' Match_Range - Range to match the criteria against
> ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
> ' if range is more than one cell it will pull the value
> ' from the same row as the application.caller
> ' Concatenate_Range - Range to concatenate text from
> ' Match_Range and Concatenate_Range must be the same size
> Dim x As Long
> Dim Criteria_Value As String
> Dim Source_Cell As Range
> Dim Match_Row_Count As Long
> If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
> Exit Function
> End If
> Set Source_Cell = Application.Caller
> If Criteria_Range.Rows.Count > 1 Then
> Criteria_Value = Criteria_Range _
> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
> Else
> Criteria_Value = Criteria_Range.Text
> End If
> ConcatenateIF = ""
> If Criteria_Value <> "" Then
> Match_Row_Count = Match_Range.Rows.Count
> For x = 1 To Match_Row_Count
> If Criteria_Value = Match_Range.Cells(x, 1).Text _
> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
> If ConcatenateIF = "" Then
> ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
> Else
> ConcatenateIF = ConcatenateIF & Chr(10) & _
> Concatenate_Range.Cells(x, 1).Value
> End If
> End If
> Next x
> End If
> End Function
>
> What am I missing?
>
> Thanks,
> Jim
>
>
> Simon Shaw wrote:
>> More Improvements: Thanks JulieD
>> __________________________________________
>>
>> Public Function ConcatenateIF(Match_Range As Range, _
>> Criteria_Range As Range,
> _
>> Concatenate_Range As
> Range) As
>> String
>>
>> ' created by Simon Shaw
>> ' Match_Range - Range to match the criteria against
>> ' Criteria_Range - Range to get the criteria to match against the
> Match_Range
>> ' if range is more than one cell it will pull the value
>> ' from the same row as the application.caller
>> ' Concatenate_Range - Range to concatenate text from
>> ' Match_Range and Concatenate_Range must be the same size
>>
>> Dim x As Long
>> Dim Criteria_Value As String
>> Dim Source_Cell As Range
>> Dim Match_Row_Count As Long
>>
>> If Match_Range.Rows.Count <> Concatenate_Range.Rows.Count Then
>> Exit Function
>> End If
>>
>> Set Source_Cell = Application.Caller
>> If Criteria_Range.Rows.Count > 1 Then
>> Criteria_Value = Criteria_Range _
>> .Cells(Source_Cell.Row - Criteria_Range.Row + 1, 1).Text
>> Else
>> Criteria_Value = Criteria_Range.Text
>> End If
>>
>> ConcatenateIF = ""
>>
>> If Criteria_Value <> "" Then
>> Match_Row_Count = Match_Range.Rows.Count
>> For x = 1 To Match_Row_Count
>> If Criteria_Value = Match_Range.Cells(x, 1).Text _
>> And Concatenate_Range.Cells(x, 1).Value <> 0 Then
>> If ConcatenateIF = "" Then
>> ConcatenateIF = Concatenate_Range.Cells(x, 1).Text
>> Else
>> ConcatenateIF = ConcatenateIF & Chr(10) & _
>> Concatenate_Range.Cells(x, 1).Value
>> End If
>> End If
>> Next x
>> End If
>>
>> End Function
>> _________________________________________________
>>
>>
>