Pivot table or formulas......help

  • Hello

    I have been working on sorting through loads of data, and fianlly i have it down to manageable size. I am now focusing on 3 columns:

    -A: this is a location of the workstation, it is a series of numbers separated by dots, ie. 16.05, 16.54.87 and so on
    -B This is the name of the staff that was located at the workstation
    -C The length of the booking for each person at each workstation

    Now the formating of this file may seem strange, but it is due to the way it was extracted from Lotus. Now for what I need:

    I have to summarize the amount of time spent by:
    1) workstation
    2) Person

    So the output column(s) should look something like this:
    for 1)
    16.05 20 days
    16.08.01 18 days

    for 2) Jane Smith 20 days
    Bob Dole 4 Days

    Any help would be much appreciated, as I have yet to figure out how to start this out. ( when i tried pivot table, it won't let me create it due to an error message regarding pivottable name).

    I have attached a sample file for a better explanation



  • Welcome to the Forum!

    Here is an example with some pivot tables in it. Your data needs to be layed out slightly differently. I have based the tables on the revised layout in the file. Hope this helps a bit. If you need further explanation, let us know. :)


  • Thanx for your help Brandtrock.
    I looked through the data, and it seems the pivot table is not correct in the totals field.
    i.e. for station 16.08.01 it should be 20 days, not 13

    Also, regarding the new formatting, looks simple enough to do, however I have over 6000 entries for which i'd have to apply this new formatting to.

    I am thus thinking maybe it should be somehow done by formulas? To simply this process, I am now only looking for summarys by workstation.

    So ideally, for each workstation, I would be able to look at how many days it was used.
    Any ideas?



  • In regards to the data not being correct that is only because the "Count" rather than "Sum" function has been used. Use the pivot table wizard to fix this.

    In every other aspect BrandTrock's advice is correct. If you're going to analyse pieces of information (records) you need to make sure that each record is complete (all fields filled in on each line) so that analysis will return the right results.

    Sure you can get round this through programming or code but the code will have to work hard and with high risk of error if you haven't structured the data correctly.

    First rule - get the structure right (fields, field value type (don't mix numbers and text ...ie don't show "5 days" as a field).

    Second rule - don't leave gabs - no blank rows or columns

    Hope this is of help.

    Robert Hind
    Perth - Western Australia

  • Sorry for overlooking the SUM for the data fields, I left it on COUNT. I am reattaching with formulas in Columns D, E, F to get the data into the format required for the Pivots. You can copy them into your sheet and then the pivot tables should work. I did not redo the pivot ranges for the formula ranges The numbers should be as you expected now.


  • In response to R Hind, the format of the data has not been created by me. it is a Lotus notes extract, and I cannot change the way it appears.
    There are gaps, because that's how the Lotus database holds the records, which I cannot fix, and I'm sure the code would be quite messy. Also the 5 days column, can bea easily converted to a number, using morefunc, a quick and simple wmid can get the required results. I left it as is because i was hoping the answer would be provided via formulas

    I looked at the revised pivot table and it is correct, however now i am not sure what to do with it since its not possible for me to reformat the 6000+ records.

    So now my question again is, can a summary of each workstation be provided via formulas? I have tried to play around with filters and countifs to get the number of staff names between stations, but I have nothing concrete yet.
    Are there any other ideas out there?

  • Brandtrock,

    In your attachment, you show the formulas used to re-format the data.

    How did u get the info from columns D:F to look like the ones in G:I. Pasting values would yield differnet results.....did u manually erase records?



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!