copy cell content based if adjacent cell meets criteria

Question

I have a series of matrices consisting of 7 columns with a varied number of rows. I want the company names that are in column 2 of the matrix if the corresponding data in column 4 is "CM" aggregated into one cell per matrix (lets say B3:B98 for all the different matrices) with a space in between the different names. Please see the below picture for an example of what the matrices look like

The end result is that all the company names in Column E will be aggregated in B3 if the cell on the same row in column G is "CM", the next matrix beginning in Column M in B4 and so on.

I am having zero success in getting my if statement to recognize "CM" in the cell content, or aggregating the results using the Join statement. Any help is much appreciated.

Edits: The objective is to have all the underwriters on a particular security aggregated in one cell, so that the cell can be easily searched in another part of the sheet for the presence of a particular underwriter.

The code below, as you can likely tell, does not work. I hit a wall as I could not get it to distinguish between cells that contained "CM" and those that did not. (I know that the code below would not aggregate the result in any cell, only copying the result into column B, as I said, it is a work in progress that has stalled.)

Dim Ws5 As Worksheet:       Set Ws5 = Worksheets(5)

'turn off some Excel functionality so code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

'Compiles the managers in the matrices into a column on the MgrMatrix sheet to be used
'for the entry sheet column of underwriters.
Dim CoL As Range:     Set CoL = Ws5.Range("D3:K104")
Dim CeL As Range

For Each CeL In CoL.Columns(4)
    If CeL.Text = "CM" Then
        CeL.Offset(0, -5) = "CM"
    Else
        CeL.Offset(0, -5) = CeL.Offset(0, -2).Value
    End If
Next

Edit: Using urdearboy's code, i modified it to work for multiple matrices on the same sheet in the below way. This version doesn't have the same finesse as his did, as this version relies on all matrices containing the same number of columns and not exceeding 100 rows.

For i = 7 To 857 Step 9
For y = 3 To 100
    If Cells(y, i) = "CM" Then
        s = s & Cells(y, i).Offset(0, -1).Value & " "
    End If
Next y

If s = "" Then
    s = "Sole Lead"
End If
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Trim(s)
s = ""

Next i

Seeing the end result photo (what are you trying to accomplish?) using your sample data would also help. — urdearboy, Jul 16 '18 at 22:09
@Jeeped I have added what little code I have managed to compile for this, — Jon0311, Jul 16 '18 at 22:39
@urdearboy The objective is to have all the underwriters on a particular security aggregated in one cell, so that the cell can be easily searched in another part of the sheet for the presence of a particular underwriter. So cell B3 would have all the companies in column E, beginning with Academy Securities ANZ Securities BBVA Securities etc.. — Jon0311, Jul 16 '18 at 22:41
Well, in that case I see no reason for your If CeL.Text = "CM" Then to fail. In any event, your solution lies with a user defined TEXTJOINIFS found [here](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|83.6653#50719050). — , Jul 16 '18 at 23:00

urdearboy · Accepted Answer · 2018-07-17T02:15:23.957

Paste code in VBE within Sheet 5 (Or whatever sheet you want to run this on).

The string, s, will build itself as it loops through your column checking for "CM" matches.

As is, the code will add commas between each new value added like, so, and, so, and then remove the last coma at the end before displaying the final string like, so, and, so

Option Explicit

Sub TextCM()

Dim i As Long, s As String

For i = 3 To Range("G" & Rows.Count).End(xlUp).Row
    If Range("G" & i) = "CM" Then
        s = s & Range("E" & i).Value & ", "     'Remove & ", " if you do not want the comma + space
    End If
Next i

Range("B2") = Left(s, Len(s) - 2)               'Change to Range("B2") = s to not delete last character in string

End Sub

You should be able to figure out how to extend this to multiple tables (matrices?) no problem.

copy cell content based if adjacent cell meets criteria

1 Answers1