How do I pivot distinct values in Excel PivotTable fields editor?

Question

I have a table layout with different number of text values for each id column. Now, I'd like to pivot them, so that each id gets a single row and each text value is put into a different column of the id row:

How can I do that using PivotTable Fields editor?

Answer 1

Dear @AxD,

Thank you for your inquiry regarding the layout of text values in a standard PivotTable.

In this case, please kindly allow me to provide some information with you: A standard PivotTable will not automatically spread each Text value into its own column for each ID. It will list them vertically.

To achieve your desired layout, I recommend using a formula-based approach leveraging Excel's dynamic array functions (available in Excel 365 and Excel 2021).

According to my test environment, here are these steps you can try and check if it helps:

Step 1: List Unique IDs

Assuming your data is in columns B (ID) and C (Text), starting from row 2, enter the following formula in cell E2:

=UNIQUE(B2:B10)

Step 2: Spread Text Values Horizontally for Each ID

In cell F2, enter the following formula and drag it to the right (e.g., G2, H2, etc.):

=IFERROR(INDEX($C$2:$C$100, SMALL(IF($B$2:$B$10=$E2, ROW($B$2:$B$10)-ROW($B$2)+1), COLUMN(A1))), "")

In older versions of Excel, please enter this as an array formula using Ctrl+Shift+Enter. In modern Excel, simply press Enter.

This method will dynamically list each text value in its own column for the corresponding ID, providing a more intuitive and structured view of your data.

I hope this information is helpful. Please follow these steps and let me know if it works for you.

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

I'm looking forward for your reply.

---

If my answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

You have to perform some steps to get that working inside a Pivot table:

1. Load the data using Power Query.
2. Group the data by ID and add a local Index to each:

Change

= Table.Group(Source, {"ID"}, {{"AllRows", each _, type table [Id=number, Text=text]}})

to

= Table.Group(Source, {"ID"}, {{"AllRows", each Table.AddIndexColumn(_,"Index", 1, 1)}})

3. Expand and load the table into the Data Model.
4. Create a Pivot table from there.
5. Add a measure MyText:

=CONCATENATEX(Table1,Table1[Text],",")

6. Drag ID into the Rows section, Index into the Columns section and MyText into then Values section.

Andreas.

Answer 3

Hi,

In cell D2, enter this formula

=LET(z,A2:A10,PIVOTBY(z,"Text "&BYROW(z,LAMBDA(a,COUNTIF(A2:a,a))),B2:B10,SINGLE,,0,,0))

Hope this helps.

Answer 4

Hi,

This Power Query M code works as well

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Text", "Index"}, {"Text", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Count", {{"Index", each "Text " & Text.From(_, "en-IN"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Text")
in
    #"Pivoted Column"

Answer 5

Hi,

Solution via formula and solution via Pivot Table.

Assuming that your Data Source data is given in cells B3:C12 then apply the following formula in cell E3.

=LET(d, IFERROR(TEXTSPLIT(TEXTJOIN(";",, BYROW(GROUPBY(B3:B12, C3:C12, ARRAYTOTEXT, 3, 0), ARRAYTOTEXT)), ", ", ";"), ""), IF(ISNUMBER(--d), --d, d))

Attached is the link where you can download the file created by me and where the solution via Power Pivot is also given using formulas in Measure.

Removed link

HTH