Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ugly PT - how to fix? (2k)

    I have a really ugly pivot table that has 3 fields (field1, field 2, field3) in the row section, and 1field (field4) in the data section. field4 is a count of field3. I'm having trouble organizing it in a way to extract the data I need, and I'd hate to have to resort to manual counting of the data <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    what I am trying to find is the count of field3 that is a subset of field2
    Field1
    Field2 item
    field3a <-
    field3b <-
    field3c <-
    Field2b
    field3a1<-
    field3a2<-count these values, and then sum
    Field1b
    ...

    but their count can be above 1 because there are multiple entries, so the totals of the field3 values I am looking for is inflated.

    Here is an example of code i used dragged down for one length of a field1 entry: =IF(RIGHT(A5,5)="Total",0,IF(RIGHT(B5,5)="Total",0 ,IF(D5>=1,1,0)))
    I then sum those values to get the number that I am looking for... but there are over 100 of these field1's that vary in length, so this isn't really efficient.

    I think this sounds very confusing, but i'm hoping someone may be able to help me find a way to get the data I am looking for in a less manually-done method
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ugly PT - how to fix? (2k)

    I am not entirely clear of the dataset you have and what number you are trying to get. COuld you elaborate or maybe attach a simple example workbook

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ugly PT - how to fix? (2k)

    here, its just a copy of the PT though, i hope thats good enough. i'll tell you as much as i can <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ugly PT - how to fix? (2k)

    wow, you are good. one question: it goes in the line below the totals, which i think would make it hard to do a vlookup to get that value... if i could actually get that sum to display in the same row where the user's name appears, i would be set. any ideas?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ugly PT - how to fix? (2k)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> OOPS typo. The formula goes in F4 not F3...
    I edited the original post to reflect this

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ugly PT - how to fix? (2k)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Feb-05 12:53. Corrected Typo)</P>How about in F4:
    <pre>=IF(RIGHT(A4,5)= "Total",COUNTA($C$4:C4)-SUM($F$3:F3),"")</pre>


    Autofill F4 from F5 to end of the table.

    You can delete column E if desired.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ugly PT - how to fix? (2k)

    awesome, i changed 1 cell around so it didn't take the row that said "Sub Number" into consideration. but I don't really understand what the formula is actually doing, i just see it highlighting stuff. how does it know what the range to be calculated is?!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ugly PT - how to fix? (2k)

    oh, i get it, it sums them up and subtracts the sums! thanks again!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ugly PT - how to fix? (2k)

    It actually counts (not sums) from the top to the current row of all the unique items, and then subtracts the sum of all the previous ones, leaving the count for the current names...

    Steve

Posting Permissions

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