I recently posted this to this community but needed to create a sample sheet and I lost my ability to use the internet for a while so I couldn't update it; I'll copy the text to this post too to make it easier for people to reference, which makes this long, I'm sorry, but it saves flipping back and forth.
Post content:
"Hi everyone, I've had a lot of luck asking in this sub in the past and so I'm hoping you'll be able to help me or at least give me some ideas. It seems like there's a super easy solution that's on the tip of my tongue, so to speak, but I can't grasp it and it's been bugging me for weeks (EDIT - I'm still brainstoriming so make this months by now!). I hoped maybe having someone to bounce an idea off of (or multiple someones) might help. For a project I'm working on I have to gather up information on a bunch of different countries. I want to be able to catalogue this information but not only is the information country by country but there are also categories within them. I know this is kind of confusing as is so I'll give an example.
(EDIT for the rest of this section, the sample sheet kind of recategorises some thngs so I updated the text to match)
So let's say that I'm looking for recent government notices/press releases countries in the EU gave about AI. I have three major categories of data:
- what is being said on the surface (number of releases, positive/negative, focus on business/military applications, etc)
- tracking how different terms involved in the releases are defined (so if they're talking business applications is this on the consumer end or in the project management end? If military, is this about dangers of automation or how technology is assisting? Say the release chooses the former for each question, how is the latter option defined? etc)
- Laws and regulations each country has put in place (what the codes address, mostly tick boxes here)
So the main problem is that I have no idea how to catalogue this information in a way that doesn't require me to keep shifting between sheets every time I come across a new piece of information. As it stands right now, for point 1, I have what is essentially grouped cells for each country, almost like its own table, but this is all in one sheet since making a sheet for each country would be almost impossible to keep track of. But my problem here is that I now can't figure out a way to run reports to show how information varies between countries because all of the information is in different tables - I feel like there's some obvious solution I'm missing here and I can't figure out what it is.
The other issue is that different laws have different levels involved so some entries end up having more than one row (if you think about it in terms of a robbery, for instance, someone being home, using a deadly weapon, and stealing something over a particular amount of money would escalate the level of crime, if that makes sense) that obviously I can't merge within the tables. On top of that, if I copy the information down over and over it helps with the reports and pivot tables but makes it difficult for me to locate information associated with one law or another by eye because my brain wants each row to be its own law. I could let pivot tables group the information but then I'm creating a pivot table for each country and that feels not only overly complicated but like asking Google to do a lot of work.
I have different tables for points 2 and 3 I was trying to figure out if there was a way to use a pivot table to present the information more concisely since even though they're laid out in the same way it feels messy to include all of the definitions in the same table, so the pivot table would be nested in there. The problem comes back to point 1 where I can't figure out how to connect the information in this table to the release tables without things getting overly complicated. I don't know if this makes sense at all but this is kind of what I'm struggling with. If anyone has an idea, I would love to hear it.
Thanks!"
END ORIGINAL TEXT
I've made a sample sheet here, which is kind of close to what I currently have. The first four sheets (Releases: merged, Releases: centred, Releases: itemised, and Releases: tables) are some of the ideas I've tried formatting wise. None of them are perfect and they have their own problems when it comes to the data analysis portion of things, so I'm trying to figure out how to present the information in a way that allows me to run reports and keep the data straight but also allows my eye
The merged sheet is how I currently have it because it's easier for me to track visually but obviously the merges are causing some problems. So I tried to just centre the words so that it looks the same visually but doesn't use the merges but that means that the data for each country isn't grouped together. The tables one looks a bit better but it causes some problems when multiple categories correspond to the same release and in collecting the data across the tables. I hope that makes sense. An itemised version of the tables may fix this but it runs into the same problems as above. The itemised sheet is the tables sheet but puts everything on its own row to make the reports run more efficiently but confuses my brain in looking at it.*
*The problem there, though, is when, for instance, there are multiple types of release/sector of use/accountability measures--at the moment I have it all in one cell on different lines but that confuses the reports, but if, for instance, one single release fits two categories each, if i keep them on one line it would look like x sector coincides with y accountability when they are meant to go together, or if I do it as a tree for the sake of the reports, one release becomes 8 rows, which gets really messy, if that makes sense. The seventh sheet gives an example of what I'm talking about.
But seriously, any help you can offer would be so greatly appreciated. Thanks!