How to Show Data

S

sjo007

New member
Joined
Dec 1, 2015
Messages
28
Reaction score
0
Points
1
Excel Version(s)
Excel for Mac 16.4
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
 

Attachments

  • example.xls
    62 KB · Views: 17
Paul,

Many thanks for the quick reply. It is column C

Best wishes,


Stephen
 
Sorry Paul - my fault and thanks for being able to look at it later.

Best wishes,

Stephen
 
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
 
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
 
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
 
Stephen

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

DateNameMember/GuestFish LocationFish Type
12/06/2017JonesNoReach 1Brown Trout Returned
12/06/2017JonesNoReach 4Rainbow 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
 
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
 
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
 
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
 
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,

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
 
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
 
How to Show data

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
 

Attachments

  • Copy of example-2A.xls
    27 KB · Views: 12
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 - a thousand thanks for this. Just the job and brilliantly done. I am most grateful.

Best wishes,


Stephen
 
You must log in or register to reply here.
Back
Top