Talk:List of countries by intentional homicide rate/Excel instructions
- Note: For another method with Excel see: Wikipedia:Reference desk/Archives/Computing/2021 October 28#How can I see only the latest year for each country in a spreadsheet?
Note: These instructions are for getting a list of latest available data by country. See result: User:Timeshifter/Sandbox213. World Bank source is using UNODC data.
See diff: "It took me about 5 minutes to find out that the LOOKUP function can help me do it and then 5 more minutes to actually do it. However, I didn't clean the data of regions (they are listed along with countries) and N/A cases."
I've just updated the table using a much simpler approach.
- Use Filters to get only "Total" values for everything and only the "Victims of intentional homicide" data. The result is saved as a separate table.
- This table is is then Filtered to show only Counts.
- It is then Sorted by Year (descending).
- The resulting rows are copied to a new sheet.
- On that new sheet, Remove Duplicates is applied based on the Country column. As a result, we now have here all the countries with latest year Count values.
- Return to the table from step 2. Switch the Filter from Counts to Rates now.
- Copy the rows to a new sheet.
- On that new sheet, Remove Duplicates is applied based on the Country column. As a result, we now have here all the countries with latest year Rates values.
- Copy all the rows to the table in step 5.
- Sort by Country. It will now have all countries with two rows per each - the latest year's with Count and Rate.
- It is then trivial to compare the existing Wikipedia table to the UNODC table. It cannot automatically generate the entire table, but when there are just a few changes to the data, it is a simple and rather fast approach.