CODEWITHSUNDEEP
vbaexcel
Kode
Kode

Reputation: 3215

Concatenate Rows with Matching IDs

I have multiple rows of purchase details. Each purchase has a client ID. For presentation purposes I need to merge purchases with a similar client ID into a single cell so I can use a VLOOKUP to display this in another table that has client information. Any ideas?

In the example below, I'd like cell C2 to contain "1, 2", cell C3 to contain "3" and cell C4 to be empty (bill has made no purchases).

     A       B      C
1 client_id name  purchase_ids
2         1 jim
3         2 bob
4         3 bill


purchase_id purchase_client_id amount
          1                  1    100
          2                  1    500
          3                  2     50

Upvotes: 0

Views: 12528

Answers (4)

SickDimension
SickDimension

Reputation: 912

Here another suggestion, do a function that gathers the data in one cell with VBA. Done this some time ago, but you can use & edit it for your own purpose -

Option Explicit
Public Function STRINGCONCATENATEVLOOKUP(ByVal r As Range, ByVal criteria As Variant, Optional ByVal colnum As Long, Optional ByVal separator As String) As String
On Error GoTo err_hand

Dim n As Long
Dim result As String

If colnum = Empty Then colnum = r.Columns.Count

If colnum > r.Columns.Count Or colnum < 1 Then
    STRINGCONCATENATEVLOOKUP = "#COLVALUE!"
    Exit Function
End If

If separator = "" Then separator = ";"

For n = 1 To r.Rows.Count Step 1
    If r.Cells(n, 1).Value = criteria Then result = result & r.Cells(n, colnum).Value & separator
Next
result = Left(result, Len(result) - Len(separator))

STRINGCONCATENATEVLOOKUP = result
Exit Function

err_hand:
    STRINGCONCATENATEVLOOKUP = CVErr(xlErrValue)
End Function

Function works just like VLOOKUP, but with the difference it sums all data and returns a string separated by ";" or what you define.

Upvotes: 2

user3616725
user3616725

Reputation: 3655

my answer requires MOREFUNC addon*

Here I assume data in purchase "table" is in A9:C11. Adjust accordingly.

formula for C2: {=MCONCAT(IF($B$9:$B$11=A2,$A$9:$A$11,""),",")}

notice the curly braces. This is an array formula you have to confirm using Ctrl+Shift+Enter, not just Enter

then copy the formula to C3 and C4

MOREFUNC ADDON

Upvotes: 0

LondonRob
LondonRob

Reputation: 78983

I'm afraid you're going to have to get your hands dirty with VBA (macro programming) to do what you want to do.

There is no Excel function which can create a concenated list. The Excel function CONCATENATE doesn't do what you need:

=CONCATENATE(A1, "-", B1) # returns "foo-bar" if A1 has 'foo' and B1 has 'bar'

So VBA is what you'll need. Fortunately, others have been here before, including this SO answer.

Upvotes: 1

Seketman
Seketman

Reputation: 144

You can create a Dynamic Pivot Table into new sheet, to summarize sales by ClientID, and then use that table with VLOOKUP (http://www.tips-for-excel.com/2011/06/how-to-make-a-pivot-table/).

Example data sheet Example data sheet

Pivot table summarized by ClientID Pivot table summarized by ClientID

Upvotes: 2

Related Questions