PDA

View Full Version : How to Show Data



sjo007
2017-06-13, 09:07 AM
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

Paul_Christie
2017-06-13, 09:37 AM
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

sjo007
2017-06-13, 01:22 PM
Paul,

Many thanks for the quick reply. It is column C

Best wishes,


Stephen

Paul_Christie
2017-06-13, 03:16 PM
Stephen

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

Paul

sjo007
2017-06-13, 03:27 PM
Sorry Paul - my fault and thanks for being able to look at it later.

Best wishes,

Stephen

Paul_Christie
2017-06-13, 03:54 PM
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

sjo007
2017-06-13, 04:36 PM
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

Paul_Christie
2017-06-14, 12:35 PM
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

Paul_Christie
2017-06-15, 10:59 AM
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

sjo007
2017-06-15, 01:52 PM
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

Paul_Christie
2017-06-15, 02:23 PM
Stephen,

Ok. It would be good if they could reconfigure the csv because in data terms it becomes what us techies call 'normalised' that's jargon for not repeating values in the same record. It might seem odd but it's better to have values duplicated in different records but not duplicated within the same record.

I'm not doing the day job for the next few days so I'll extend my thoughts and drop you back a sample. There may be a way of creating the structure that best suits a summary without changing the csv.

By the way, where are you based in time zone terms?

Paul

sjo007
2017-06-16, 06:13 AM
Paul,

I have spoken to the form manufacturer and it is going to be a long drawn out process to use their API to produce the data as you have suggested above. Therefore if possible, I would really appreciate your thoughts on how best progress using the format I attached in my original attachment. I accept that I may have to manipulate the spreadsheet to first get the entries as per your suggested and much easier to manipulate format.

I am on BST - British Standard Time. Many thanks for all your help with this - much appreciated.

Best wishes,


Stephen

Paul_Christie
2017-06-16, 08:19 AM
Stephen

That's good at least we're in the same zone. I communicate quite a bit with Ken Puls and get used to posting stuff at the end of the day and getting a response the following morning. I'll get back to you later today.

Paul

Paul_Christie
2017-06-16, 09:32 AM
Stephen

I've done the attached as Pivot Tables. Is this close to what you wanted?

Paul

sjo007
2017-06-18, 07:43 AM
Paul,

That is brilliant - just the job. Many thanks indeed !

2 final questions if I may:

- first - how did get from my example to where it had name and the details of the fish caught and the category all in one row - to your Sheet 1 where it listed the date, name, member/guest. fish location and Fish type ? Did you do that my hand and going through each entry or was there a way to automate this please by the use of formulas to produce the table ?

- second - is more of general question - in a table such as Sheet 1 - where there is a list of dates that cover a number of months, is there a way to change the individual date to show just the month only ?

Many thanks for all your help with this - much appreciated.

Best wishes,


Stephen

Paul_Christie
2017-06-20, 08:53 AM
Stephen

I reorganised the data using simple copy and paste. I appreciate that with such a small quantity of data that's easy and in the past on Windows Excel I would have created a macro. If I did it now I'd use Power Query. I know you can't use macros on the Mac but I don't know about Power Query. I'll investigate. I might also be able to to do something just using table addressing but let me think about that one.

To change the date into just the month you could just add a column called month and and use the formula =MONTH(A2) or similar. The advantage with using formulas in tables is that the formula populates automatically.

What I posted was done using Pivot Tables. I've got a stopover tomorrow evening so idle time in hotel. I'll redo what I posted without using Pivots so that you can see the alternative way of doing it.

Paul

Paul_Christie
2017-06-20, 01:02 PM
Stephen

Slightly ahead of what I promised I've done some more with your file, see attached.
I've created Sheet4 & Sheet5 which do the same as Sheet2 and Sheet3 but using functions referring to the table. I've also added the Month column to the table. To my mind the benefit of doing it this way is that you can control what your table looks like. With a Pivot Table the Pivot will adjust as the data changes. Ok, if that's what you want but doing it the way I do it you can control what happens and if you put some sort of control total to the bottom right of the table and then apply a conditional format then you can instantly see if your row/column labels need adjusting. This is a better way of finding spelling mistakes by suddenly noticing that you pivot has changed because someone can'e spell Reech.

I need to sort my Microsoft account out because at the moment I'm limited to what I can do on the iPad. All I've described should work on Mac Excel but I do need to test it myself.

Paul

sjo007
2017-06-21, 12:16 PM
Paul,

Grateful thanks but the copy attached is not the right one I think - please can you resend. Many thanks for all your help with this.

Best wishes,


Stephen

Paul_Christie
2017-06-21, 01:14 PM
Stephen

Sorry about that, should have the right one now.

Paul

sjo007
2017-06-21, 01:43 PM
Paul - a thousand thanks for this. Just the job and brilliantly done. I am most grateful.

Best wishes,


Stephen