+ Reply to Thread
Results 1 to 4 of 4

Pivot table to identify number of times a value appears in column

  1. #1
    Registered User
    Join Date
    10-12-2016
    Location
    deal
    MS-Off Ver
    unsure
    Posts
    2

    Pivot table to identify number of times a value appears in column

    I have previously created a pivot table in Microsoft Excel 2007 for a column of names and it gave me the option to drop row fields in one box and data fields in another box. i have tried to do this with a column of vehicle indexes but it does not give me the same options when creating the pivot table. what do i need to do to be able to identify how many times in that column a certain vehicle index appears and also is there any way of creating a pivot table for a large number of entries as it only seems to work up to about 35000 in names

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,245

    Re: Pivot table to identify number of times a value appears in column

    Add the "Vehicle Index" field to your Pivot Table in the "Rows" section.
    Add the "Vehicle Index" field to your Pivot Table again, in the "Values" section.


    See attachment for a quick worked example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    10-12-2016
    Location
    deal
    MS-Off Ver
    unsure
    Posts
    2

    Re: Pivot table to identify number of times a value appears in column

    Ok thanks that's great very much appreciated! So now I have vehicle indexes and then a value next to them for how often they appear in the column. How do I now filter these two columns so that I can have the vehicle indexes that appear the most at the top of that list?

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,245

    Re: Pivot table to identify number of times a value appears in column

    Click the down arrow button on the "Row Labels" header
    Click 'More Sort Options...'
    Click 'Descending (Z to Z) by:
    Choose 'Count of Vehicle Index' in the dropdown list.
    Click 'OK'

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating number of times individual number appears in a pivot table
    By snoopy1461 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-15-2014, 08:39 AM
  2. Counting the number of times the same data appears in a table
    By dispro14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2014, 07:04 AM
  3. Replies: 5
    Last Post: 10-22-2012, 10:26 PM
  4. [SOLVED] find number of times a letter or a number appears in a column
    By dcoates in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2012, 02:47 PM
  5. Replies: 2
    Last Post: 03-14-2012, 08:12 AM
  6. Counting the number of times a range of values appears in a table.
    By mikenola in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-19-2010, 12:52 PM
  7. Replies: 1
    Last Post: 03-11-2009, 03:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1