+ Reply to Thread
Results 1 to 5 of 5

Listing more than one date in Pivot Table

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Kent
    MS-Off Ver
    365
    Posts
    20

    Listing more than one date in Pivot Table

    I have a database that has more than one date listed under the same ID on sheet 2. I have extracted the table into a Pivot Table but can only get "MIN" and "MAX" returns as results but i also need all other dates under the same ID to be listed.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,406

    Re: Listing more than one date in Pivot Table

    Hi,

    Just drag the Date Field into the Rows area of the PT Field List underneath the ID.

    It's not immediately obvious to me why you need to use MAX & MIN to find the first and last dates. Why not just set the PT to show the PT sorted in date order?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    251

    Re: Listing more than one date in Pivot Table

    Hi mikey

    Is this what you want ?

    you need to right click on the year/quarter/... to group it after dragging Date to Row


    Since you are using Excel 2016/O365, why don't consider using Power Query for grouping your data in different perspective,

    it is quite a friendly and powerful tool
    Attached Files Attached Files
    Christopher Yap

  4. #4
    Registered User
    Join Date
    05-14-2015
    Location
    Kent
    MS-Off Ver
    365
    Posts
    20

    Re: Listing more than one date in Pivot Table

    Hi Richard
    I trying to extract three dates against the same ID to use in a spreadsheet table using an imported data. I did use VLOOKUP but it only returns the first date under Misuse criteria it comes to within that Column which is no good, and also tried AGGREGATE but that returns "SMALL" OR "LARGE".
    Misuse
    Id Location Name Events 1st Event 2nd Event 3rd Event
    2125 Location A 3 28/09/2017 17/01/2018 03/07/2020

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,406

    Re: Listing more than one date in Pivot Table

    What do you want to see? If you mock up an example we can then decide what is the most appropriate.
    As you have found, stuff like LOOKUP, INDEX MATCH etc. only return a single value

    I had assumed you wanted all dates for an ID in the Pivot Table which is why I suggested adding the dates in the Rows field and then having the PT display dates in Ascending order.

    If you want a straightforward table returned it may be that a simple Advanced Data Filter run by a macro might be more appropriate than a PT.

    In the meantime maybe something like the attached.
    Attached Files Attached Files

+ 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. Pivot table - listing
    By tomitza in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-11-2013, 10:04 AM
  2. [SOLVED] Listing unique values without Pivot Table.
    By bungslea in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2013, 12:19 AM
  3. Listing unique values and summing w/o a pivot table
    By Drew Goldberg in forum Excel General
    Replies: 24
    Last Post: 02-16-2013, 09:52 PM
  4. [SOLVED] pivot table not listing things in one row
    By COLTS12OG in forum Excel Charting & Pivots
    Replies: 43
    Last Post: 01-29-2013, 06:35 PM
  5. Pivot table with columns listing counts
    By theThirdMan in forum Excel General
    Replies: 2
    Last Post: 12-10-2012, 06:20 PM
  6. [SOLVED] Macro to update pivot item (date format) in pivot table to latest date from data source.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2012, 03:18 AM
  7. listing names marked yes - pivot table?
    By eamexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2010, 11:37 AM

Tags for this Thread

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