CODEWITHSUNDEEP
excelexcel-formulapivot-table
MCardinale
MCardinale

Reputation: 1406

How to count multiple values in one single cell using Pivot Tables in Excel?

I have this table in a Excel sheet:

enter image description here

I would like to create a Pivot Table to have this outcome:

enter image description here

Do you have any idea how to do it?

Upvotes: 3

Views: 12973

Answers (2)

Samet Baykul
Samet Baykul

Reputation: 21

You can solve this problem even without pivot table. I solved my same problem by this way.

I supposed in second table, "Item" is in A1 and "Count of Table" is in B1; and first table is in "Sheet1" and second table is in "Sheet2"

You can also add a new column for the amount of fruit which bought by a customer. This is in Sheet1, D1 column.

You can put such a formula under "Count of Item" Column for each row: (You just write this formula for once and extend to bottom as usual)

=SUMIF(Sheet1!$C$2:$C$5;"*"&A2&"*";Sheet1!$D$2:$D$5).

Upvotes: 2

nicholas79171
nicholas79171

Reputation: 1273

If you're okay with using helper columns, use text to columns to split up your third column into individual columns by using commas as the delimiter. Get everything in one column and then create your pivot table on the resulting data.

EDIT:

Select C2:C5 and go to the data tab and select "Text to Columns" bringing up a dialogue box. Choose delimited and then check the comma box on the next screen as well as the space option to get rid of the spaces and hit finish. You should have each item in its own cell.

Next, we need to get everything in one column. There's not a great way to do this without VBA (VBA is required if you have a lot of data) but what you can do for smaller data sets is copy the entire row, right click at the top of an empty column, choose Paste Special and check the Transpose option. This will take the row of data that you copied and paste it in the destination as a column, or vice versa if you copied a column. Repeat for all rows of data until you have one nice column of all the fruits (including duplicates).

It sounded like you have a grasp of pivot tables so I won't go into detail on that, but I can if you need me to.

EDIT2:

Here's a link to some options for converting tables to one column other than copy and paste. I'd recommend using the following code:

Sub ConvertRangeToColumn()
    'Updateby20131126
    Dim Range1 As Range, Range2 As Range, Rng As Range
    Dim rowIndex As Integer
    xTitleId = "KutoolsforExcel"
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
    rowIndex = 0
    Application.ScreenUpdating = False
    For Each Rng In Range1.Rows
        Rng.Copy
        Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        rowIndex = rowIndex + Rng.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions