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


 * Note: For another method with Excel see: Reference desk/Archives/Computing/2021 October 28

Excel (2016 version) instructions for World Bank source
''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
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.