Results 1 to 9 of 9

Thread: Add column with auto increment number in existing PIVOT table

  1. #1
    Seeker AlexBN's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    365

    Add column with auto increment number in existing PIVOT table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am uploading simple sample with my issue. I have created PIVOT and visually formatted it as I want. Now, I need to add column before first pivot column with auto increment number. I can add it and auto fill it, but it is not part of PIVOT, and also it is not colored as pivot. I am asking is it possible to add it to be part of pivot, also when I add new data in Base table, that column should be auto filled ad long as there is new rows.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,893
    Articles
    0
    Excel Version
    365
    Attached has a table at cell H3 of the Pivot sheet which you can refresh like a Pivot table but it's not a Pivot table. I can't guarantee that the index will stay constant for a given Ime - you'd need a separate table for that (or have it in you pivot's source data).
    Attached Files Attached Files

  3. #3
    Seeker AlexBN's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Attached has a table at cell H3 of the Pivot sheet which you can refresh like a Pivot table but it's not a Pivot table. I can't guarantee that the index will stay constant for a given Ime - you'd need a separate table for that (or have it in you pivot's source data).

    Thank you. p45cal. Your job is working. But problem is that I don't know what you did. If you can explain how does it work. Thank you and best regards.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,893
    Articles
    0
    Excel Version
    365
    It's Power Query (aka Get & Transform Data). See the Data tab of the ribbon, sections Get & transform Data and Queries & Connections.
    You can right-click the table, choose Table, then Edit Query…

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    291
    Articles
    0
    Excel Version
    2019
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

  6. #6
    Seeker AlexBN's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    It's Power Query (aka Get & Transform Data). See the Data tab of the ribbon, sections Get & transform Data and Queries & Connections.
    You can right-click the table, choose Table, then Edit Query…

    Thank you. This exceeds my knowledge of the Excel. I will add it manually out of pivot table. Best regards.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,893
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by AlexBN View Post
    I will add it manually out of pivot table
    Love it.

    Put the string Index in cell E1 of your Base sheet
    Put a 1 (one) in cell E2
    In E3 put:
    Code:
    =IFERROR(VLOOKUP($A3,$A$2:$E2,5,0),MAX($E$2:$E2)+1)
    copy down.
    Extend your pivot table's source range to include column E
    Add the Index field as the first item in the Rows area of the pivot table
    In the Design tab choose Show in tabular form in the Report Layout dropdown of the Layout section.
    Hide the +/- buttons.

    See your pivot table tweaked at cell B3 of the Pivot sheet in the attached.
    Attached Files Attached Files

  8. #8
    Seeker AlexBN's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    365
    p45cal, thank you for helping. Unfortunately this solution doesn't help. Maybe I wasn't clear. This Pivot table is intended to show order of best player in some game. After making Pivot it should show rank list. But when I change values of average in Base table (some player get better score) this Index column behaves as ID of that player. In Pivot table I don't want that. I want to have order in Rank list. And if Slobodan is for example worst player it must be on bottom and number before him is Ranking 5 not his index which is 1

  9. #9
    Seeker AlexBN's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    365

    Solution!

    I think I found solution. I added column Sum of Average one more time and called it Rank. Than right-click on any values in that column and the rank value is created setting "Show values as" to "Rank largest to smallest", with Ime set as the Base field. Than I Sort value in column with clicking Auto sort by Sum of Average. And I got what i lookin for.
    P45cal thank you for helping.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •