CODEWITHSUNDEEP
excelconcatenation
User247365
User247365

Reputation: 685

conditional concatenation of cells based on cell values

I have a table with data spans from C24:H28. There are headers for each column in row 23 (Note that the data is not formatted as a table, just a spreadsheet). In cell A1, I want to display the header names for any column containing data less than a value of 100 separated by a comma. I know EXCEL's CONCATENATE function is what I am looking for here, but I am not sure how to create the formula to conditionally concatenate based on the contents of the columns.

A truncated example is below:

Tom     Joe     Bob   ...
125     245     325  ...
60      600     164  ...
305     20      410  ...

I would want cell A1 to read Tom, Joe

I am hoping to avoid Macros if possible.

Upvotes: 0

Views: 2101

Answers (2)

R. Roe
R. Roe

Reputation: 609

You could use an if countif formula inside the concatenate formula.

Something like this:

=CONCATENATE(IF(COUNTIF(K14:K16,"<100")<>0,K13&", ",""),IF(COUNTIF(L14:L16,"<100")<>0,L13&", ",""),IF(COUNTIF(M14:M16,"<100")<>0,M13&", ",""))

The range in the countif is the numbers you want to check against and the true statement cell "K13" etc. is the header you want to concatenate. I'm sure this could all be turned into an array formula but those get kind of scary.

Hope this gets you in the right direction though.

lol, was just a few minutes too late, but I'd do the macro different if a macro was involved. I'd use a function instead of a subroutine so that you could call it in a cell like =CustomConcat(Range to check)

Upvotes: 1

Tony M
Tony M

Reputation: 1762

This should work, but it's ugly and doesn't do the commas. But it's a good start for you. 

=CONCAT(IF(MIN(C24:C28)<100,C23,"")," ",IF(MIN(D24:D28)<100,D23,"")," ",IF(MIN(E24:E28)<100,E23,"")," ",IF(MIN(F24:F28)<100,F23,""),," ",IF(MIN(G24:G28)<100,G23,""),," ",IF(MIN(H24:H28)<100,H23,""))

Much cleaner if macros were involved:

Sub lessThan100()
Dim r As Range, aR As Range, i As Integer, j As Integer, less As Boolean
Set r = Range("C23:H28")
Set aR = Range("A1")
aR = ""
For i = 1 To r.columns.Count
  less = False
  For j = 2 To r.Rows.Count
    If r(j, i) < 100 Then
      less = True
      Exit For
    End If
  Next j
  If less Then aR = aR & r(i) & ", "
Next i
If Right(aR, 2) = ", " Then aR = Left(aR, Len(aR) - 2)
End Sub

Upvotes: 0

Related Questions