Excel - concatenating cells where reference number matches

Question

I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to concatenate the data contained in, say, column B from both of those rows to a new cell in, say, column C.

Example:

Col A      Col B         Col C                    
12345     Robert      Robert, David      
12345     David        Chris                    
54321     Chris                                     

Answer 1

Here's the VBA approach. Assuming Excel 2007 or later; first few steps will differ if you're using a pre-Ribbon version of Excel.

1. Enable the Developer Tab

2. Click on "Visual Basic" on the Developer Tab on the Ribbon

3. Right-click on "VBAProject (whatever-your-sheet-is-named.xls*)" and do Insert -> Module in the context menu

4. Paste in this code

   Option Explicit
   
   Function allquixotic(param As Variant, search As Range, values As Range, Optional absolute As Boolean = False) As String
   
   Dim sep As String, retval As String
   Dim i As Integer, rownum As Integer
   Dim look As Range, j As Range
   
   sep = ", "
   retval = ""
   For i = 1 To search.Rows.Count
   Set look = search.Cells(i, 1)
   If absolute Then
           rownum = look.Row
   Else
           rownum = i
   End If
   
   If look.Value = param Then
           If absolute Then
                   Set j = values.Worksheet.Cells(rownum, values.Column)
           Else
                   Set j = values.Cells(i, 1)
           End If
           retval = IIf(retval = "", retval & j.Value, retval & sep & j.Value)
   End If
   
   Next
   
   allquixotic = retval
   
   End Function
   

5. Use the worksheet function (feel free to rename it) by using a formula like

   =allquixotic(A1,$A$1:$A$15,$B$1:$B$15,true)
   

6. Use the fill handle to put the formula in all the cells

The parameters are the following:

=allquixotic(look_cell, key_range, value_range, absolute)

look_cell: The first parameter, should be a single cell or a value literal. Valid input includes things like 3, $6.25, "Hello", etc. This is the value that you are trying to find in key_range.

key_range: This should be a range of cells (more than one cell); if absolute is true then you will get very strange results unless this is a contiguous range (all the values are in sequential rows).

value_range: This should be a range of cells (more than one cell); if absolute is true then you will get very strange results unless this is a contiguous range (all the values are in sequential rows).

absolute: If true, then we will use the absolute row number (relative to the number of rows in the entire spreadsheet) of each "found" row in the key_range to determine what row to extract a value from value_range on. If false, we will use relative numbers; for instance, if we find a match in the third row of key_range, then we will extract the value from the third row of value_range. Recommended value is FALSE, or you can omit it to default to that.

Note: This function does not support the case where the key and value ranges are in columns, but it should be fairly easy to adapt it to that.

Also, if you specify multiple columns in either the key_range or the value_range, only the leftmost column will be used.

Answer 2

Would this work for you? Set the formula in C2 as

=IF(A2=A3,B2 & ", " & B3,B3)

It assumes your data is sorted on ColA so that reference numbers are grouped together.

QUESTION: Can there be more than 2 names with the same reference number in ColA?