Problemset

Bina Nusantara Skill Competition
Qualification Round
Sunday, 5 August 2007

Topic : Spreadsheet – Data (Sort-Pivot-Subtotal)

Please download the result and content in here

Instruction:
Save with filename: “Answer SS - Data (Sort-Pivot-Subtotal).xls"

Instruction :

Rename “Sheet 1” into “Data”

• Sort
  • Add a new sheet “Sort” after sheet “Data”
  • Copy all data in “Data” to “Sort”
  • Sort the data in sheet “Sort” as follow :
    • Date – ascending
    • Class – ascending
    • Category, with this list :
      • Cartoon – Serial – Fiction – True Story – Drama – Action – Thriller – Humour
      • Ascending
    • Code - ascending
  • Don’t resort the Number in field “No”

• Subtotal
  • Add a new sheet “Subtotal” after sheet “Sort”
  • Copy all data in “Data” to “Subtotal”
  • Make a subtotal like in “Report.PDF”, (included in rar file )
    • Make a page break for each category, and repeating row (not as HEADER) for each page

• Pivot
  • Add a new sheet “Pivot” after sheet “Subtotal”
  • Copy all data in “Data” to “Pivot”
  • Make a pivot as seen on picture below (or in “Pivot.jpg”) in new sheet named “PivotResult” (additional field in sheet “Pivot” is not allowed)
  • Add new group named “Request Category” based on “total income” :
    • A : total income from 3500 - < 30000
    • B : total income from 30000 - < 60000
    • C : total income > 60000
  • Add new group named “Week” based on “date" :
    • First     : 1 – 7 July 07
    • Second : 8 – 14 July 07
    • Third    : 15 – 21 July 07
    • Fourth  : 22 – 28 July 07
    • Fifth     : 29 – 31 July 07