CODEWITHSUNDEEP
excel
RRM
RRM

Reputation: 3451

Group several rows into single field in Excel

I have data in Excel in following format

Employee  Company
John      A
George    A
Bob       A
Peter     B
Luke      B

and I would need:

Company Employees
A       John,George,Bob
B       Peter,Luke

Is there an easy way of doing it in Excel?

Upvotes: 2

Views: 2083

Answers (4)

user4039065
user4039065

Reputation:

String concatenation over more than a few cells is best left to a VBA User Defined Function (aka UDF) even without setting criteria. Your situation of an unknown number of strings and applying revolving criteria would certainly fit this category.

Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).

Public Function conditional_concat(rSTRs As Range, rCRITs As Range, rCRIT As Range, Optional sDELIM As String = ", ")
    Dim c As Long, sTMP As String
    For c = 1 To Application.Min(rSTRs.Cells.Count, rCRITs.Cells.Count)
        If rCRITs(c).Value2 = rCRIT.Value2 Then _
            sTMP = sTMP & rSTRs(c).Value & sDELIM
    Next c
    conditional_concat = Left(sTMP, Application.Max(Len(sTMP) - Len(sDELIM), 0))
End Function

Tap Alt+Q to return to your worksheet. Use this UDF like any native Excel worksheet function. The syntax is,

conditional_concat(<range of strings>, <range of conditions>, <cell with condition>, [optional] <delimiter as string>)

      Conditional String Concatenation

The formula in E2 is,

=conditional_concat(A$2:A$99, B$2:B$99, D2)

Fill down as necessary. I've used the optional sDELIM parameter to provide semi-colon delimination in E3 with,

=conditional_concat(A$2:A$99, B$2:B$99, D3, "; ")

Upvotes: 0

Jerry
Jerry

Reputation: 71538

I posted an answer to something similar a while ago. The same principle could apply here, though it's easier in your case.

  1. First, make sure that the list is sorted by column B.

  2. In cell C2, you can put:

    =IF(B2=B3,0,1)

  3. We'll use this later on. In cell D2, put:

    =IF(B1=B2,CONCATENATE(D1,", ",A2),A2)

  4. Drag/Fill the two formulae down and you should get a full list on each cell where you have 1 in column C. Copy/Paste values on the formulae, then apply a filter. Select all the 0 in column C and delete all the records (in columns A through D). After that remove the filter and sort by any column.

Upvotes: 4

Kirk H
Kirk H

Reputation: 451

So! You'd like to do a concatenate if match style comparison/lookup. Unfortunately there's nothing out-of-the-box in Excel for this, but this slightly more complex Q&A thread outlines two potential approaches:

  1. Using the MoreFunctions add-in for Excel
  2. Using a custom "MultiLookup" VBA function

http://answers.microsoft.com/en-us/office/forum/office_2003-excel/if-vlookup-is-match-then-concatenate/26f6794d-8663-4e59-9a1b-a598c78f3bed

Both approaches require a little advanced knowledge of Excel (VLOOKUP, compound functions, etc). If you're not familiar with these things then VLOOKUP is a good place to start!

Upvotes: 0

will
will

Reputation: 312

I think you want the concatenate function:

http://office.microsoft.com/en-ca/excel-help/concatenate-HP005209020.aspx

Upvotes: 0

Related Questions