CODEWITHSUNDEEP
excelvbaconditional-statementsconcatenation
theWolf
theWolf

Reputation: 37

Concatenate certain cells from column based on two conditions

I have a Excel worksheet of different football teams and their squads, and information about the individual players. What I am trying to do is, from my table, concatenate certain cells and place that data into a cell on a more simpler table for other users to see, for instance, to show which players are injured currently in all the teams. I'll explain:

F_Team     | Player   | Injured
Liverpool   Coutinho       0
Liverpool   Benteke        1
Liverpool   Sturridge      1
Man U       Rooney         1
Chelsea     Sterling       0

So in my other table it looks like this

F_Team     | Players Injured
Liverpool   Benteke, Sturridge
Man U       Rooney

So the data can be grouped into the individual teams, I am just stuck trying to concatenate it properly.

I have tried using this VBA, but all it comes back with is #NAME? and I don't know why, and I don't know if what I am doing is correct.

Function ConcatenateIf(CriteriaRange As Range, criteriarange2 As Range, _
Condition As Variant, condition2 As Variant, ConcatenateRange As Range, _
Optional Separator As String = ",") As Variant 'Update 20150414

Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If

For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition And criteriarange2 = condition2   Then
    xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i

If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

And the formula I am using:

=CONCATENATEIF($D$2:$D$20000, $L$2:$L$20000, Z2, 1, $E$2:$E$20000)

Column D is F_Team Column E is Player Column L is Injured Column Z is what F_Team is to match against Column D

Upvotes: 0

Views: 903

Answers (1)

A.S.H
A.S.H

Reputation: 29332

If CriteriaRange.Cells(i).Value = Condition And criteriarange2 = condition2 Then

Should be:

If CriteriaRange.Cells(i).Value = Condition And criteriarange2.Cells(i).Value = condition2 Then

p.s: in my test, with the original code I did not get #NAME, but a full list of all the players. That is because of the On Error Resume Next statement. I highly recomment to omit this statement: since this is a UDF, if the user types it wrongly, let Excel handle the error its own way (display #VALUE), rather than displaying whatever wrong data.

Upvotes: 1

Related Questions