Concatenate cells meeting certain criteria

  • Thread starter Thread starter agrandstaff
  • Start date Start date
A

agrandstaff

Say in row 2, all the way across (B2:IV2) there are people's names in
each cell (ie Jon Smith, Kate Jones, etc). In row 5 there are notes
about these people (text notes about their performance). I am trying
to write a formula which will say, "Whenever you find the name John
Smith in any column in row 2, take all of the notes from the same
columns in row 5 and concatenate them all in this cell". I've been
trying to do it using a mix of SUM and IF as you would do for numbers
but I can't get it to work. Anyone have any ideas?
 
Say in row 2, all the way across (B2:IV2) there are people's names in
each cell (ie Jon Smith, Kate Jones, etc). In row 5 there are notes
about these people (text notes about their performance). I am trying
to write a formula which will say, "Whenever you find the name John
Smith in any column in row 2, take all of the notes from the same
columns in row 5 and concatenate them all in this cell". I've been
trying to do it using a mix of SUM and IF as you would do for numbers
but I can't get it to work. Anyone have any ideas?
Click to expand...

The problem is that CONCATENATE won't work on arrays. It only returns the
first argument.

Two options:

1. If none of the notes are >255 characters, you could download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/morefunc/english/
and use the MCONCAT function in an array formula (entered by holding down
<ctrl><shift> while you hit the <enter> key. Excel will place braces {...}
around the formula).

=TRIM(MCONCAT(IF(B2:IV2="Jon Smith",B2:IV2," ")))

You can use a cell reference containing the name, in place of the name itself.

2. Use a UDF that does the same thing. To enter this, <alt-F11> opens the
VBEditor. Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that opens. Then
use the formula

=ConcatNotes(Subject, Names, Notes)

==================================
Option Explicit
Function ConcatNotes(Subject, Names As Range, Notes As Range) As String
Dim c As Range
For Each c In Names
If c.Value = Subject Then
ConcatNotes = ConcatNotes & c.Offset(Notes.Row - c.Row).Value & " "
End If
Next c
ConcatNotes = Application.WorksheetFunction.Trim(ConcatNotes)
End Function
===============================

--ron
 
The problem is that CONCATENATE won't work on arrays.  It only returns the
first argument.

Two options:

1.  If none of the notes are >255 characters, you could download and install
Longre's free morefunc.xll add-in fromhttp://xcell05.free.fr/morefunc/english/
and use the MCONCAT function in an array formula (entered by holding down
<ctrl><shift> while you hit the <enter> key.  Excel will place braces {....}
around the formula).

=TRIM(MCONCAT(IF(B2:IV2="Jon Smith",B2:IV2," ")))

You can use a cell reference containing the name, in place of the name itself.

2.  Use a UDF that does the same thing.  To enter this, <alt-F11> opens the
VBEditor.  Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that opens.  Then
use the formula

=ConcatNotes(Subject, Names, Notes)

==================================
Option Explicit
Function ConcatNotes(Subject, Names As Range, Notes As Range) As String
Dim c As Range
For Each c In Names
    If c.Value = Subject Then
        ConcatNotes = ConcatNotes & c.Offset(Notes.Row - c.Row).Value & " "
    End If
Next c
ConcatNotes = Application.WorksheetFunction.Trim(ConcatNotes)
End Function
===============================

--ron
Click to expand...

Thanks Ron, in the UDF is the 'c' where the cell containing the Name
goes?
 
Thanks Ron, in the UDF is the 'c' where the cell containing the Name
goes?
Click to expand...

Don't change anything in the VBA code.

As the code runs, "c" will step through each cell containing your names in row
2, and sequentially test it against Subject. If there is a match, then the
corresponding entry in the Notes range will be concatenated.

As I wrote, you enter the formula:

=ConcatNotes(Subject, Names, Notes)

in your worksheet.

Subject is the Name you are searching for.
Names is your range which contains your various names.
Notes is your range which contains your various notes corresponding to the
names.

So, if you wanted to concatenate all the notes belonging to Kate Jones, your
formula might look like:

=ConcatNotes("Kate Jones",B2:IV2,B5:IV5)

Of course, if you had the name Kate Jones in A3, you could also enter the
formula as:

=ConcatNotes(A3,B2:IV2,B5:IV5)
--ron
 
Don't change anything in the VBA code.

As the code runs, "c" will step through each cell containing your names inrow
2, and sequentially test it against Subject.  If there is a match, then the
corresponding entry in the Notes range will be concatenated.

As I wrote, you enter the formula:

=ConcatNotes(Subject, Names, Notes)

in your worksheet.

Subject is the Name you are searching for.
Names is your range which contains your various names.
Notes is your range which contains your various notes corresponding to the
names.

So, if you wanted to concatenate all the notes belonging to Kate Jones, your
formula might look like:

=ConcatNotes("Kate Jones",B2:IV2,B5:IV5)

Of course, if you had the name Kate Jones in A3, you could also enter the
formula as:

=ConcatNotes(A3,B2:IV2,B5:IV5)
--ron- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks very very much Ron. It works great. Your help is most
appreciated.
 
Thanks very very much Ron. It works great. Your help is most
appreciated.
Click to expand...

You're welcome. Glad to help.

Take note that your Names and Notes must start in the same column (as you
described it). If one is offset from the other, the function will need
modification.
--ron
 
You're welcome.  Glad to help.

Take note that your Names and Notes must start in the same column (as you
described it).  If one is offset from the other, the function will need
modification.
--ron
Click to expand...

Ron,

Is it possible to concatenate yet another row with the Notes row?
IE . .again say there are Names in Row 2, Notes in Row 5, but also a
Date in Row 4. Would it be possible to say, "In any column you find
John Smith concatenate the Dates in Row 4 with the Notes in Row 5, but
all together? So using the example below . . .

Row 2 John Smith Jane Doe Lisa Johnson John
Smith John Smith
Row 4 5/22/08 5/23/08 5/23/08
5/24/08 5/25/08
Row 5 John is Tall Janie isnt Lisa left
John said so John left early

Is it possible to concatenate in one cell that would result in one
cell. . . .

5/22/08 - John is Tall. 5/24/08 - John said so. 5/25/08 - John left
early
 
Ron,

Is it possible to concatenate yet another row with the Notes row?
IE . .again say there are Names in Row 2, Notes in Row 5, but also a
Date in Row 4. Would it be possible to say, "In any column you find
John Smith concatenate the Dates in Row 4 with the Notes in Row 5, but
all together? So using the example below . . .

Row 2 John Smith Jane Doe Lisa Johnson John
Smith John Smith
Row 4 5/22/08 5/23/08 5/23/08
5/24/08 5/25/08
Row 5 John is Tall Janie isnt Lisa left
John said so John left early

Is it possible to concatenate in one cell that would result in one
cell. . . .

5/22/08 - John is Tall. 5/24/08 - John said so. 5/25/08 - John left
early
Click to expand...

Shouldn't be much of a problem. Try something like:

================
Option Explicit
Function ConcatNotes(Subject, Names As Range, _
Dates As Range, Notes As Range) As String
Dim c As Range
For Each c In Names
If c.Value = Subject Then
ConcatNotes = ConcatNotes & _
c.Offset(Dates.Row - c.Row).Text & " - " & _
c.Offset(Notes.Row - c.Row).Value & ". "
End If
Next c
ConcatNotes = Application.WorksheetFunction.Trim(ConcatNotes)
End Function
==============================
--ron
 
Shouldn't be much of a problem.  Try something like:

================
Option Explicit
Function ConcatNotes(Subject, Names As Range, _
        Dates As Range, Notes As Range) As String
Dim c As Range
For Each c In Names
    If c.Value = Subject Then
        ConcatNotes = ConcatNotes & _
            c.Offset(Dates.Row - c.Row).Text & " - " & _
            c.Offset(Notes.Row - c.Row).Value & ". "
    End If
Next c
ConcatNotes = Application.WorksheetFunction.Trim(ConcatNotes)
End Function
==============================
--ron- Hide quoted text -

- Show quoted text -
Click to expand...

Worked like a charm. Thank you so much again Ron!
 
Back
Top