Microsoft Excel Users

Microsoft Excel Users

60,729 members
  • Join

    When you join a group, other members will be able to see your profile and message you. The group logo will be visible on your profile unless you change that setting.

  • Information and settings

Have something to say? Join LinkedIn for free to participate in the conversation. When you join, you can comment and post your own discussions.

David

Pivot Table for Scouting

Business Process and Operations Management

I have been working on a huge pivot table and have hit a part that I am at a loss of complete. It is for my scout troop and shows all scouts with ages, ranks, merit badges, etc.

In the Row Label I have Program, Age, Birth Date, Scout, and Rank. Current the results show all the ranks, but here is the part I can't figure out, how do I show just the last rank earned. The data base has the dates.

Any ideas?

Thanks,

Dave

  • Comment (8)
  • May 30, 2012
  • Close viewer

Comments

  • EDWARD F.

    EDWARD

    EDWARD F.

    President at true north usa, llc

    David,

    That's a tough one but maybe not...

    If Rank happens to be a number and rank only increases but never decreases over time ....then you are home free as you would just add Max of Rank as a value field and Scout Name as a row label.

    I am thinking that is too easy ...so if Rank is not a number but rather a text field....then if you are willing to keep your data sorted by Name Ascending and then Date Ascending and if the last rank earned is signified by the latest date ..maybe its not so tough again.

    In that case
    If Column C is the scout "Name" column and row 2 is the first row of actual data appearing in the list ..........

    you would create a new column on far right of table (say Column X) called CurrentStatusFlag

    where
    X2=IF(C2<>C3,TRUE,FALSE)
    Drag the contents of X2 down for all the data rows (or copy X2 and paste in all used X rows)

    Include column X in your pivot data source and set the column CurrentStatusFlag as a Pivot Report Filter ( aka Page Field) .

    When CurrentStatusFlag is set to TRUE the pivot will only display the latest entry for each scout ...including his Rank if it is a Pivot Row Label.

    Remember, the latter approach will only work if you keep your pivot source data table sorted by Name First and Then Date ascending.

    Hope this helps,
    ed (:))

  • Terry P.

    Terry

    Terry P.

    Information Technology Professional

    You mentioned a database. Is Excel linked to an external database? If so then create a query within the database that selects just the latest rank earned by each scout and link to that query.

  • EDWARD F.

    EDWARD

    EDWARD F.

    President at true north usa, llc

    David,

    One more important comment ....I wish I had made before...

    If every young boy could find his way to become a Boy Scout (or its equivalent) …this country and the planet would make a giant, dramatic and lasting shift for the good!

    I just wanted to say how appreciative I am of your involvement with Scouting and wish you always good luck in what you are doing.

    Best,
    Ed ()

  • David W.

    David

    David W.

    Business Process and Operations Management

    Ed - Great ideas. I had not thought of that before. I do not think it will work because of the way we input all the scout awards (merit badges and advancements.) We input one award at a time and it is a combo of merit badges and rank advancements. I think that makes it impossible to sort. I did add a number to the rank (1-tenderfoot, 2-2nd class, 3-1st class, etc) That allowed me to sort in the right order in the pivot table. I will have to look at the "Max of Rank" option and see if that will work.

  • David W.

    David

    David W.

    Business Process and Operations Management

    Terry - Thanks for the note. The database is in excel on another sheet. That is what makes it hard

  • Joe D.

    Joe

    Joe D.

    Business Process Manager at Eaton Corporation

    David - What Ed is suggesting can work you just need a more comprehensive check. For example if names are in col A, Events (Rank Changes, Merit Badges, etc) are in Col B, Date event occurred in col C, and our Last rank Indicator is in col D. And there is a table of the available ranks named "Ranks".

    Sort the data by name and then Date Descending.....
    Then in cell D2, you can put the equation - "=IF(ISERROR(VLOOKUP(B2,Ranks,1,0))=TRUE,"",IF(SUMIF(A$1:A1,A2,D$1:D1)=0,1,""))"

    This checks to see if the event is a rank change, and if yes then checks to see if a rank change is already listed above the current row for this boy. If not then put a 1 in Col D.

    Then in the pivot table, you can only dispay data where this check is a 1.

    If you send me your email, I can send you the quick sample I made up.

    I would also like to thank you for your service to the scouts. It is a great organization that I enjoyed being a part of as a boy and with my son.

  • Terry P.

    Terry

    Terry P.

    Information Technology Professional

    David, I know not everyone has access to Microsoft Access or a similar desktop database, but I believe you'd be better served by implementing your system using a desktop database. Spreadsheets have their uses, but they're really not good databases.

Have something to say? Join LinkedIn for free to participate in the conversation. When you join, you can comment and post your own discussions.

Your group posting status

Your posts across groups are being moderated temporarily because one of your recent contributions was marked as spam or flagged for not being relevant. Learn more.

Feedback