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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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 />
 
Upvote 0

Forum statistics

Threads
1,214,324
Messages
6,118,892
Members
448,854
Latest member
Eduard_Stoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top