Comparing monthly numbers of different pests around parts of the building
Hi everyone, I don't have heaps of experience with excel and am finding this hard with my current data formatting and excel skills.
I have a spreadsheet that monitors the types and numbers of pests/insects etc. in each pest trap around the building. Currently the traps are checked once a month and changed if needed, with this data entered into a spreadsheet. Each monthly survey is in a new sheet, with the exact same format. There are a lot of pest traps, so each monthly sheet is quite long.
I am trying to find a way to summarize monthly pest numbers by pest type and by 'location' (not trap number), eg. south basement, ground floor annex south, etc. - so far I am able to do this with a simple pivot table (see screenshot). What I haven't worked out, however, is how to compare this month's pivot table to last month's.
Every cell in the "pest type" column has a drop down list of all the pest types - it would be great if the summary table could show all pest types, even if they were not present.
Also, a previous spreadsheet with different sheets for each building level, rather than survey month, was used in the past - it had an "analysis sheet" where you could copy and paste the previous month's table a few rows below and would have to change the formula every time to adjust to the data new source location and destination location (if this makes sense).
I am also happy to format differently if it helps with summarizing the data, even if it requires a complete restructuring. Also let me know if this post is not appropriate/if there is somewhere else it would be better to post.
Thanks! :)
[link] [comments]
Want to read more?
Check out the full article on the original site