Selectively Concantenating Cell Values

Relysis

New Member
Joined
Sep 7, 2010
Messages
20
I hope this is possible, but I can't quite manage it. I want to add all values in column B per distinct value in column A in a new column, separated by a comma. Here's what it would look like:

Code:
ID    Color    (What I Need)
 
1    Red       Red, Blue, Green, Yellow
1    Blue      Red, Blue, Green, Yellow
1    Green     Red, Blue, Green, Yellow
1    Yellow    Red, Blue, Green, Yellow
2    Orange    Orange, White 
2    White     Orange, White
Any ideas? Thanks in advance.
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Relysis

New Member
Joined
Sep 7, 2010
Messages
20
I'm starting to think this is only possible with vb code (which means I don't stand a chance). I'll be up all night doing this manually with 6,000 rows...
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Is this any good?

Code:
Sub Colors()
Dim Area As Range, LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        Rows(i).Insert
    End If
Next i
Columns("A").Copy Destination:=Range("Z1")
Columns("A").Delete
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(Area.Rows.Count, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Insert
Columns("Z").Copy Destination:=Range("A1")
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
 

UniMord

Active Member
Joined
May 6, 2002
Messages
309
I have an answer for you, it doesn't require VBA, but it's a little convoluted. Bear with me...

I'm assuming you don't have duplicate pairs of ID & Color.


Let's say your 2 columns are in A2:B6000.

First you will need to add 2 helper columns.
  1. In C2 enter: =IF(A2<>A1, 1, C1+1) and copy down.
  2. In D2 enter: =IF(COUNTIF($A$2:$A$6000, A2)=C2, TRUE, FALSE) and copy down.
  3. In E2 enter: =IF(C2 = 1, B2, E1 & ", " & B2) and copy down.
Now add AutoFilter, and set Column D to TRUE, and you've got what you need.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I don't understand the original question, but in reply to the last post, the IF is not necessary.

=COUNTIF($A$2:$A$6000, A2)=C2

will return a TRUE/FALSE value on its own.
 

Relysis

New Member
Joined
Sep 7, 2010
Messages
20
Thanks a lot everyone for the help. Unfortunately, I could not get the vba to work (the fault is on my end I'm sure).

I found a solution, though the COUNTIF may be more elegant. I'll try it out when I get home.

=IF(A2=A1,C1 & ", " & B2, B2)

This adds the value from each cell in B2 to the previous cell's string in a new column C. So the last cell for each unique value in column A will contain all the relevant values from column B.

Then

=IF(A2=A3,"",A2)

When added to a pivot table, I can filter out the blanks in that column to only give me the "total" cells from column C. Rough, but it works!

Thanks again for all the help.
 

UniMord

Active Member
Joined
May 6, 2002
Messages
309
If you only want the full string once, then my first solution does the trick.
If you want the full string for every row, which is how you showed it in your question, it can also be done, but, it'll be a few more steps.

Again, assuming you don't have duplicate pairs of ID & Color, and the list is sorted by ID.

Let's say your 2 columns are in A2:B6000.


First you will need to add 2 helper columns.
  1. In C2 enter: =IF(A2<>A1, 1, C1+1) and copy down.
  2. In D2 enter: =COUNTIF($A$2:$A$6000, A2)=C2 and copy down.
  3. In E2 enter: =IF(C2 = 1, B2, E1 & ", " & B2) and copy down.
  4. Copy Column E and Paste Special / Values (deletes formulas).
  5. Add AutoFilter, and set Column D to FALSE.
  6. Select visible cells in Column E (Alt+E,G,S,Y) and hit Delete.
  7. Type: =, hit the down arrow, Control+Enter.
  8. Clear Filter.
  9. Optional: Repeat Instruction #4.
 
Last edited:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try this ConcatIf function created by ZVI.

To use it, you need to first install it into your worksheet by pressing Alt+F11 to open the VBA window, then Insert>Module, then copy/paste this code into the module.

Then, in your worksheet, use the following formula illustrated below (with ranges adjusted to fit your true range)

Code:
' ZVI:2011-03-09 [URL]http://www.mrexcel.com/forum/showthread.php?t=534231[/URL]
' Function to return concatenated values of RngToConcat for which Condition happens
' Optional Delim argument (comma) is the delimiter of the resulting list
' Note1:   It's faster if performed as array formula via Ctrl-Shift-Enter
' Note2:   Decimal separator of numbers is the dot char for all localizations
' Example: =ConcatIf(A1:A10, (B1:B10>1)*(C1:C10<10), CHAR(10))
Function ConcatIf(RngToConcat As Range, Condition, Optional Delim$ = ",") As String
  
  ' Variables are reserved for:
  ' a - for array/value of RngToConcat, processing of array is faster than of range
  ' b - for array/value of Condition, used also for parsing of Condition part from the caller formula
  ' r - row counter, rs - rows count
  ' c - column counter, cs - columns count
  ' s - concatenated string, used also as temporary variable
  ' x - temporary variable
  ' vt - vartype of x-variable
  ' In Dim the suffix "&" is equal to "As Long", "$" =  "As String"
  Dim vt As VbVarType, A, B, i&, r&, rs&, c&, cs&, s$, x
  
  ' Copy range value to variable, processing of VBA array is much faster than processing of range
  A = RngToConcat.Value
  
  ' If only 1 cell is on the RngToConcat, then a-variable is not array, else it's array
  If Not IsArray(A) Then
    ' There is only single cell in RngToConcat - process it and exit
    If Condition Then ConcatIf = A: Exit Function
  End If
  
  ' Copy Condition to b-variable
  B = Condition
  
  ' If formula was entered as array formyla by CSE, then b-variable is array, else it's not array
  If Not IsArray(B) Then
    ' Ctrl-Shift-Enter was not performed - do evaluation of Condition formula(s)
    
    With Application.ThisCell
      ' Application.ThisCell.Formula gives the formula of the caller cell
      
      ' Temporarily split that formula with comma delimiter into zero based b() array
      B = Split(.Formula, ",")
      
      ' b() now is zero based array, b(1) consists Conditional equation
      ' It is assumed that comma is not used in Conditional equation, if does than more strong parsing is required
      s = B(1)
      
      ' If optional Delim was not passed into function then delete right round bracket
      If UBound(B) = 1 Then s = Left$(s, Len(s) - 1)
      
      ' Evaluate Condition in the sheet with caller cell
      ' Application.ThisCell.Parent is reference to that sheet
      B = .Parent.Evaluate(s)
      
      ' Clean s for later usage
      s = ""
      
    End With
  
  End If
  
  ' Calc rows count in b()
  rs = UBound(B, 1)
  
  ' Calc columns count in b()
  cs = UBound(B, 2)
  
  ' Loop through b() array,
  ' if not Error/False/Empty/Zero condition found
  ' then get value from a() at the same row & column for concatenation
  For r = 1 To rs
    For c = 1 To cs
      x = B(r, c)
      If VarType(x) <> vbError Then
        ' value of b(r, c) is not error - process it
        If x Then
          ' value of b(r, c) is not False/Zero/Empty - get value from a() at the same row & column
          x = A(r, c)
          vt = VarType(x)
          If vt <> vbError Then
            ' value of a(r, c) is not error - process it
            If Len(x) Then
              ' value of a(r, c) is not empty - provide dot char as the decimal separator for all localizations
              If IsNumeric(x) And vt <> vbString Then x = Trim$(Str(x))
              ' Concatenate the result in temporary s-variable
              s = s & x & Delim
            End If
          End If
        End If
      End If
    Next
  Next
  
  ' Set result
  i = Len(s)
  
  ' Delete the last comma in s
  If i Then ConcatIf = Left$(s, i - Len(Delim))
  
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Color</td><td style=";">(What I Need)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1</td><td style=";">Red</td><td style=";">Red, Blue, Green, Yellow</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">1</td><td style=";">Blue</td><td style=";">Red, Blue, Green, Yellow</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1</td><td style=";">Green</td><td style=";">Red, Blue, Green, Yellow</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">1</td><td style=";">Yellow</td><td style=";">Red, Blue, Green, Yellow</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">2</td><td style=";">Orange</td><td style=";">Orange, White</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style=";">White</td><td style=";">Orange, White</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=concatif(<font color="Blue">$B$3:$B$8,$A$3:$A$8=A3,", "</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top