4

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                                     

5
  • 1
    In your example, which is very clear and well laid out, col C is Surname, FirstName - I'm not sure if the order is going to be possible unless the surname always occurs before the firstname (reading down the column(). Is that a problem? +1
    – Dave
    Mar 5, 2013 at 14:54
  • @DaveRook I don't think the OP has Col C as Surname, FirstName it's a combination of values from Col B. Robert and David are the first two values and because they both have 12345 in Col A the OP wants them together. Mar 5, 2013 at 15:00
  • Brad is correct. Col C is just a place to dump the concatenation. I don't mind which order they go in particularly, but even if I did, it seems like that's a separate issue.
    – Lewes Dev
    Mar 5, 2013 at 15:04
  • This one is so well formatted and the other is so poorly formatted that I can't tell if this is really a duplicate. Also there's no mention of avoiding VBA in this question.
    – rtf
    Mar 5, 2013 at 21:30
  • The fun with this begins when you try to pull it off with a formula ;) And I've seen some of the excel wizzards do that :)
    – helena4
    Jun 30, 2016 at 17:17

2 Answers 2

2

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.

1
  • Thanks, allquixotic. And I didn't rename the function as your name is fantastic!
    – Lewes Dev
    Mar 7, 2013 at 15:20
-1

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?

3
  • Sadly, as the document is likely to change, shift about and grow, having absolute references to cells makes it a little difficult to implement. I was hoping for something a little more flexible.
    – Lewes Dev
    Mar 5, 2013 at 15:19
  • I think you will need a VBA macro to loop the columns Mar 5, 2013 at 16:53
  • 2
    Those aren’t absolute references; they are relative (offset) references. If they were absolute references they would have dollar signs ($) in them. As shown above, you can extend (drag/fill) cell C2 to C3 and you will automagically get =IF(A3=A4, B3 & ", " & B4, B4). Mar 5, 2013 at 18:28

Not the answer you're looking for? Browse other questions tagged .