Excel VBA Code - Concatenate Multiple Cells with Condition

Andrew O'Connor
Relken Engineering
Have a question or want to speak with Andrew O'Connor ? Contact us with your details.

Description

This Excel VBA Code concatenates cells using a conditional list (like SUMIF) and allows you to set the delimiter between the cell values. While this has endless uses, I have used it hear to automatically create the "Attended" and "Not Attended" header to minutes based on an attendance list. This useful code was obtained from Mr Excel.com forums here.

Features

This VBA code returns the concatenated values from a range of cells with the following options:

  • Optional conditional concatenation (like SUMIF).
  • Optional delimiter. In the example I use a carriage return to separate the values.
  • Option to remove duplicates.

Use and Example

  • =CONCATIF(A1:A5) to concatenate the values from A1 to A5.
  • =CONCATIF(A1:A5, "Yes", B1:B5, ",", TRUE) to concatenate unique values from B1 to B5 which have a "Yes" in the A column separated by a comma.

 

Meeting attendance to be concatenated        Concatenated attendance               

Related Functions

There are no related functions

File Attachment: 
PreviewAttachmentSize
Meeting Minutes.xlsm40.2 KB
  1. Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
  2. Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
  3.  
  4. 'CONCATIF concatenates a range of cells using a criteria and deliminator
  5. '
  6. ' This VBA code returns the concatenated values from a range of cells with the following options:
  7. ' Optional conditional concatenation (like SUMIF).
  8. ' Optional delimiter. In the example I use a carriage return to separate the values.
  9. ' Option to remove duplicates.' This function will use a simply iterative method when MU is less than 10. When
  10. '
  11. 'SYNTAX
  12. ' X = ConcatIf(stringRange)
  13. ' X = ConcatIf(stringRange, xCriteria)
  14. ' X = ConcatIf(compareRange, xCriteria, stringRange, Delimiter, NoDuplicates)
  15. '
  16. ' compareRange is the range of cells which are compared against the criteria
  17. ' xCriteria is the criteria against which a cell will be included in the concatenation
  18. ' stringRange is the range of strings to return for concatenation
  19. ' Delimited is the delimiter between string values when concatenated
  20. ' NoDuplicated is a TRUE/FALSE on whether duplicate values are allowed
  21. '
  22. 'EXAMPLE
  23. 'To concatenate the values from A1 to A5.
  24. ' =CONCATIF(A1:A5)
  25. '
  26. 'To concatenate unique values from B1 to B5 which have a "Yes" in the A column separated by a comma.
  27. ' =CONCATIF(A1:A5, "Yes", B1:B5, ",", TRUE)
  28. '
  29. ' Author: Andrew O'Connor <andrew.oconnor@relken.com>
  30. ' Date: 01 Sep 2015
  31. ' Copyright: 2015 Relken Engineering
  32.  
  33.  
  34. Dim i As Long, j As Long
  35. With compareRange.Parent
  36. Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
  37. End With
  38. If compareRange Is Nothing Then Exit Function
  39. If stringsRange Is Nothing Then Set stringsRange = compareRange
  40. Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
  41. stringsRange.Column - compareRange.Column)
  42.  
  43. For i = 1 To compareRange.Rows.Count
  44. For j = 1 To compareRange.Columns.Count
  45. If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
  46. If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
  47. ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
  48. End If
  49. End If
  50. Next j
  51. Next i
  52. ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
  53. End Function