Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: How to Show Data

  1. #1
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4

    How to Show Data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am doing a fishing catch return and the csv table of the output of my form is attached.

    Depending on the number of fish caught upto 3 in the attached example, depends on the number of entries showing location and category of fish caught per person. 1st fish, 2nd fish etc

    I am trying to extract the following information:

    - date (by month), person, guest or not, reach fished and category of fish caught. (each entry can have 1 ,2 or 3 entries of location and category of fish caught)

    Is this possible by the use of a pivot table and if so how please - or should I produce another table from this one using the COUNTIF function and then to a pivot table ?

    Any help and advice gratefully received.

    Thank you very much

    Stephen
    Attached Files Attached Files

  2. #2
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    153
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Stephen

    Sorry to make a perhaps obvious observation but I don't see a column in your attachment that gives the member/guest status.

    Paul

  3. #3
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Paul,

    Many thanks for the quick reply. It is column C

    Best wishes,


    Stephen

  4. #4
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    153
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Stephen

    Sorry, missed that, text wrap might have helped. I'll look in more detail later.

    Paul

  5. #5
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Sorry Paul - my fault and thanks for being able to look at it later.

    Best wishes,

    Stephen

  6. #6
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    153
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Stephen

    What version of Excel are you using? I've started to play with it using an Excel Table and have noticed it's in 2003 format. Anyway, I've started by adding a Fish Count column with the following formula '=COUNTA(F2,H2,J2)' which you might need anyway.

    Paul

  7. #7
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Thanks Paul. I am using Excel for Mac - the latest version but I saved the example sheet in .xls

    Thanks for the Fish Count Column but I am trying to get my head around how I tie the fish count to the relevant reach ?

    Best wishes,


    Stephen

  8. #8
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    153
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Stephen,

    Sorry for delay, day job getting in the way at the moment. I've transferred your spreadsheet to my iPad so I make sure I stay compatible with you. I'll work on it this evening UK time.

    Paul

  9. #9
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    153
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Stephen

    Thinking about your fishing problem, is it feasible to change the data structure very slightly. I'm thinking of something like this:

    Date Name Member/Guest Fish Location Fish Type
    12/06/2017 Jones No Reach 1 Brown Trout Returned
    12/06/2017 Jones No Reach 4 Rainbow Trout Killed

    I know this involves repeated records for the same person but it will make summation easier and would lend itself to pivoting better.

    Paul

  10. #10
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    23
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Many thanks Paul. I thought about this but to get another entry per person it would mean that the member would need to enter another catch return. It is bad enough getting each member to submit their catch return on line and if I asked for more entries then the reply would be unfavourable.

    That said, I will go back to the support desk of the manufacturer who provides the form and see if I can get the csv output reconfigured to what suits us better. More to follow shortly. Thanks once again for the help.

    Best wishes,


    Stephen

Page 1 of 2 1 2 LastLast

Posting Permissions

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