+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Pivot Table Display

  1. #1
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Pivot Table Display

    Hi can anyone help?
    I am trying to create a table with lots of differing data and the easiest way seems to be to use pivot tables. However I do not want to count or sum or use any other formula with the results, I just want it to display the data. eg If I was tracking the change of car wheels and wanted to display that on a certain date car 1 had its front left tyre changed on another its rear right, and so on and so forth for numerous days and tyres and cars, but wanted to amalgamate this into a large table showing date down the left, car number at the top and the tyre changed against the date and car, how would I go about this without manually inputiing?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot Table Display

    The Data Field in a Pivot Table can not display Text (barring the v.limited Custom Format route)

    It would be a good idea to post some sample data and outline desired results.

    Also specify if you're looking for VBA / formula based options - and which version of XL you're running this on.

  3. #3
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Re: Pivot Table Display

    It is in excel 2007. Here is a sample of the data. What I want is Unit along the top axis caompleted on the left and actual task to be data that is attached to the unit and the date. I could do this manualy but there is 1900 lines of this data.

    Unit Actual Task Completed
    377101 B1 23/04/2009
    377101 AS1 28/05/2009
    377101 C2 06/07/2009
    377101 UAT 06/07/2009
    377101 AS1 12/08/2009
    377101 AS6 23/08/2009
    377101 B1 18/09/2009
    377101 AS1 22/10/2009
    377101 B2 29/11/2009
    377101 AS1 05/01/2010
    377101 B1 11/02/2010
    377101 AS1 19/03/2010
    377102 B1 02/04/2009
    377102 AS1 10/05/2009
    377102 AS6 17/05/2009
    377102 B2 23/06/2009
    377102 AS1 23/07/2009
    377102 B1 26/08/2009
    377102 AS1 07/10/2009
    377102 UAT 13/11/2009
    377102 C2 14/11/2009
    377102 AS1 19/12/2009
    377102 B1 04/02/2010
    377102 AS1 14/03/2010

  4. #4
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Re: Pivot Table Display

    Sorry,

    Should have attached this instead.
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot Table Display

    Might be an idea to outline the desired layout given sample - the key will be how you intend to handle multiple tasks occurring on the same date for a given unit (eg 377101: 06/07/2009 C2 & UAT)

  6. #6
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Re: Pivot Table Display

    The UAT data isn't essential. That can be added manually later

  7. #7
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Re: Pivot Table Display

    There shouldn't be any more conflicting data.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot Table Display

    Going forward assume that we know nothing of the data - what it constitutes, the significance of the acronyms etc... we don't after all.

    My question was more to do with the fact that where you have 1+ records for an intersect point what are your intentions in terms of display ?
    (do you want to merge the values into a single cell (delimited / wrapped text) or do you want to use multiple cells - if so in which direction (multiple rows / multiple columns))

    Or, by the above are you saying that UAT records should be ignored and as a result there will only ever be at most 1 Task record per intersect ?

    The multiple records per intersect is the biggest hindrance in doing this via formulae as it would be pretty inefficient... which reminds me that you've yet to state preference re: VBA / formula solution.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Pivot Table Display

    I'm heading off line now for a bit - suffering with man-flu - unable to concentrate as a result unfortunately...

    In the interim perhaps the below might help ?

    (note: the attached makes use of IFERROR which is exclusive to 2007 and beyond - should you try / need to run this on earlier versions you will need to adjust for this with a LOOKUP based approach)
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-04-2007
    Posts
    42

    Re: Pivot Table Display

    That is exactly what I need. Many Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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