Search code examples
excelexcel-formulapivotpivot-table

Excel - Change presentation of two fields with pivot

I don't even know what to phrase the question, but I this source data:

enter image description here

and I simple want to change the presentation, so that I have a column for each customer. The end result should look like this:

enter image description here

I thought this could be done with a pivot table, but when I transform it into a pivot then the cells are empty, because I don't have a value field and I don't know how to calculate it.

Who can help me?

Solution

  • For general purpose of a presentation, you can use the Pivot Tables for the required output, by placing the Customer once in Columns area and once in Values area.

    Once you place them in Values area, select the region which shows the counts and hit CTRL+1.

    On doing above it opens the Format Cells window --> From Number Tab --> Under Category --> Click Custom and type by removing and hit OK.

    [=1]"X";

    This will change the 1s to X but note that since cell formatting is facade the formula bar would always read as 1 but the pivot table display will be showing as X

    enter image description here

    An alternative approach using Excel Formulas viz.

    LET( ) | TOROW( ) | UNIQUE( ) | MMULT( ) | HSTACK( ) | VSTACK( ) | IF( )

    enter image description here

    • Formula used in cell D1

    =LET(
     a,B2:B9,
     b,A2:A9,
     ua,TOROW(UNIQUE(a)),
     ub,UNIQUE(b),
     c,MMULT(--(TOROW(b)=ub),--(a=ua)),
     HSTACK(VSTACK("Food",ub),
     VSTACK(ua,IF(c>0,"X",""))))