0

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
LondonRob
  • 73,083
  • 37
  • 144
  • 201
Kode
  • 3,073
  • 18
  • 74
  • 140
  • 2
    Likely downvoted because you are asking for someone to do your work for you. See the [How to ask](http://stackoverflow.com/help/how-to-ask) page to see how to get the most benefit from this site. – guitarthrower Aug 26 '14 at 20:49
  • Take a look at the How to ask page, please. this site works differently than other forums. – guitarthrower Aug 26 '14 at 21:05
  • Fair enough, I rescind my comment, as I frustrated with this issue. – Kode Aug 26 '14 at 21:06
  • 1
    I read your question again to see if I missed something. There are so many questions I could ask you that should be in the question already. What do you mean by `merge`? What does your final table look like? What does your original data look like? What have you tried already? – guitarthrower Aug 26 '14 at 21:08
  • Fair question. I have two tables, 1 for client data and another for purchases. Each purchase has a client id that is the same as the client id in the client data table. I need to show all the purchases in a single cell (not ideal, but a requirement) in the client data table. – Kode Aug 26 '14 at 21:39
  • Great edit to the question. That now gives others the information needed to get you a meaningful answer. Keep it up! – guitarthrower Aug 27 '14 at 15:30
  • Thanks to @LondonRob for helping me along. Both of you (@guitarthrower and @LondonRob) have been awesome. – Kode Aug 27 '14 at 16:11
  • 2
    About your bounty, what do you mean by **credible / official sources?** – Han Soalone Aug 29 '14 at 06:50

4 Answers4

2

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

Seketman
  • 144
  • 1
  • 5
  • Thanks, but I am looking to merge rows with a common ID so I can display them in a cell on another table, as I need the other table columns. – Kode Aug 26 '14 at 21:05
2

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Han Soalone
  • 902
  • 8
  • 18
1

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.

Community
  • 1
  • 1
LondonRob
  • 73,083
  • 37
  • 144
  • 201
  • Thanks, I just discovered this and it works great, but doesn't filter out values if I filter the table. http://www.mrexcel.com/forum/excel-questions/365691-concatenate-multiple-cells-array-formula.html Any clues on how to make it respect filtered values? Thanks for all your help. – Kode Aug 27 '14 at 13:05
  • 1
    @Kode Ask a new question about this specific matter! It should be written generically (i.e. not about your specific situation) and should include the code as you have it so far. That's how Stack Overflow works! – LondonRob Aug 27 '14 at 14:45
  • Here is a more detailed question: http://stackoverflow.com/questions/25515606/excel-index-match-returning-results-for-filtered-out-data Still shifting to the Stack Overflow mindset. My apologies. – Kode Aug 27 '14 at 15:19
0

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

user3616725
  • 3,485
  • 1
  • 18
  • 27