Thread: Can you dump multiple lines of vertical data into one cell?

I have a worksheet where i want to take all the pick tickets for a p.o and dump them into one cell with a comma separating each of them. I could use an AND formula, but the worksheet is a living document that changes daily and it will be used by someone who's not excel savvy. And more importantly, I want as much of the worksheet as possible to update on its own.

I need a formula in the cell to say "Dump all the pick tickets for a p.o into this cell and separate them with a comma"

I should also note that all of the pick tickets are going down the sheet in case that makes a difference.

I feel like it would be a vlookup and an array formula, but I cannot figure it out.

I've attached an example of what i want it to do. There's 3 tabs, the raw data which is what my pivot table will pull from, the Before Pivot which will show how it currently looks because I can't figure out the formula, and the After Pivot which will show what I want the end result to look like.

Try this user defined function

Function CommaList(SourceRange As Range) As String
Dim Cell As Range
For Each Cell In SourceRange
    If Cell.Value <> "" Then
        CommaList = CommaList & Cell.Value & ","
    End If
Next Cell
If Len(CommaList) > 0 Then CommaList = Left(CommaList, Len(CommaList) - 1)
End Function

Paste this into a new VBA module (ALT F11 then Insert - Module) and then add the the sheet like a normal function.

e.g. =CommaList(B2:B8)

If you're saying the values in raw data are ordered by PO# (implied by sample) then:

'Raw Data'!C2
=IF(COUNTIF($A$2:$A2,$A2)=1,$B2,INDEX($C$1:$C1,MATCH($A2,$A$2:$A2))&","&$B2)
copied down

'Raw Data'!E1: Bucket [ie header value]

'Raw Data'!E2:
=LOOKUP($A2,$A$2:$C$20)
copied down

Revise the PT source to include column E and use this in your Pivot (rather than C).

The above means all is formula driven and dynamic though I would reiterate the above is based on assumption that data is sorted by PO#
(ie utilises binary search for optimisation)

Originally Posted by juniperjacobs

Donkey,
Yours kind of makes sense but I can't get it to work. A. How do i run it once it's in there and B, it's giving me a compile error on each line of formula.

I'm not quite sure what you mean by a Compile Error (that's a VBA term) but based on your locale of US the suggestions should work without issue.

Originally Posted by juniperjacobs

also, it seems like what you sent is dependant on the Pick Ticket bucket, but that bucket is where the formula will be entered, the way I listed it in the example is what the final result should look like. In other words, in the actual file, that column is blank and I want all the pick tickets to pull into it.

Not so. The formulae when entered in the cells designated will generate the requested results - attached.

I had presumed that by adding a working example with PT output the below would all be rather transparent, seemingly not.

As mentioned in my first post - the "final" ticket bundle for the given PO is generated in Column E not Column C and the Pivot uses the new column as source (not Col C).

Col C in this approach acts as a "running count" string - nothing more (it is not the final output as used in the Pivot)
That is to say the string extends with each PO transaction as a new ticket is added to the bundle.

Col E subsequently references the last "running count" string in Col C for the given PO thereby ensuring that all transactions for any given PO use the final complete ticket bundle string for that PO.

Given Col E now holds the final bundle string for the PO we use this in the Pivot.

The reason we use 2 columns [Col C & E] for this formula driven approach is efficiency.