...
When clicking on the new value in the column "Appended Village", you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).
How to quickly eliminate duplicate entries from your Lookup list:
Use: To link villages to districts, you need one row per village. But because multiple villages can belong to a single district, this means as a result that you will have several rows where your district is repeated. CommCare will require a separate list for districts and a separate one for villages. For that reason, you will want to eliminate all the duplicate districts from your Excel spreadsheet.
How: (numbered steps correspond to the images below)
1) Select the columns containing duplicate data. Click "Data" on the toolbar, then click "Remove Duplicates"
2) Make sure that you select that your data has headers (if it does), and that you have selected the entire column. Click OK.
3) You will see a message box with the number of duplicate values that have been removed and the number remaining.
If you want to be able to see which duplicate values were removed
- In a new tab, copy - paste your original column with duplicates into column A
- Copy - paste your new cleaned up column into column B
- In column C, enter the following countif equation: "=COUNTIF(B1:B800, A1)"
- In any row that has the value "2" in column C, the column A value is a duplicate
How to (very) quickly fill in a column with a value or a formula:
If you want to quickly pull down a single value, you can double click (don’t drag) on the bottom right corner and the value will fill in bellow for all rows where there is information next to it.
The RED X shows you were to double click.
AND the same thing can be done with Functions! But don't forget to copy and paste value once you've created a column of functions.
