Taking multiple values in one cell and making it so a pivot table can be run

Final goal: Make Pivot Tables

Problem: Data coming in poorly and needs to be worked on in order to do pivots.

All the values come in one cell (see below)

Need to change it to one value per row in order to run pivots (see below)

I can split the values into different columns (see below)

But, as it is, it doesn't really help.

Using formula, you can try something along the lines of:

• Formula used in cell <kbd>D1</kbd>

=REDUCE(A1:B1,SEQUENCE(ROWS(A2:B6)),
LAMBDA(x,y,VSTACK(x,IFERROR(HSTACK(INDEX(A2:B6,y,1),
TEXTSPLIT(INDEX(A2:B6,y,2),,",")),INDEX(A2:B6,y,1)))))

Alternative approach without using <kbd>LAMBDA( )</kbd>:

• Formula used in cell <kbd>D1</kbd>

=LET(
     α,A1:A6,
     ß,B1:B6,
     σ,TEXTBEFORE(TEXTAFTER(","&ß,",",SEQUENCE(,MAX(LEN(ß))))&",",","),
     φ,HSTACK(TOCOL(IFNA(α,SEQUENCE(,COLUMNS(σ)))),TOCOL(σ)),
     FILTER(φ,ISTEXT(INDEX(φ,,2))))

However, using <kbd>POWER QUERY</kbd> will be the easiest approach.

To perform the above task, follow the steps:

• First convert the source range into a table and name it accordingly, for this example I have named it as CStbl

• Next, open a blank query from <kbd>Data</kbd> Tab --> <kbd>Get & Transform Data</kbd> --> <kbd>Get Data</kbd> --> <kbd>From Other Sources</kbd> --> <kbd>Blank Query</kbd>

• The above lets the Power Query window opens, now from <kbd>Home</kbd> Tab --> <kbd>Advanced Editor</kbd> --> And paste the following <kbd>M-Code</kbd> by removing whatever you see, and press <kbd>Done</kbd>

let
    Source = Excel.CurrentWorkbook(){[Name="CStbl"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Comma Separated Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comma Separated Values")
in
    #"Split Column by Delimiter"

• Lastly, to import it back to Excel --> Click on <kbd>Close & Load</kbd> or <kbd>Close & Load To</kbd> --> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result --> Since you need the end result as <kbd>Pivot Table</kbd>, you can select the <kbd>Pivot Table</kbd> or you can use <kbd>Table</kbd> and then transform to <kbd>Pivot Table</kbd> from Excel or you can click on <kbd>Only Create Connection</kbd> to use it.

While power query does the trick best, this formula does as described:

=LET(a, A2:A6,
     b, B2:B6,
     c, DROP(REDUCE("",SEQUENCE(ROWS(a)),
             LAMBDA(x,y,
             VSTACK(x,
                    INDEX(a,y)&","&TEXTSPLIT(INDEX(b,y),,",")))),
             1),
HSTACK(TEXTBEFORE(c,","),
       TEXTAFTER(c,",")))