Talk:List of countries by intentional homicide rate/Excel instructions

Excel (2016 version) instructions for World Bank source

edit

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."

Excel (2016 version) instructions for direct UNODC source

edit

I've just updated the table using a much simpler approach.

  1. 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.
  2. This table is is then Filtered to show only Counts.
  3. It is then Sorted by Year (descending).
  4. The resulting rows are copied to a new sheet.
  5. 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.
  6. Return to the table from step 2. Switch the Filter from Counts to Rates now.
  7. Copy the rows to a new sheet.
  8. 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.
  9. Copy all the rows to the table in step 5.
  10. Sort by Country. It will now have all countries with two rows per each - the latest year's with Count and Rate.
  11. 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.