I have a spreadsheet in which columns can have values among a set of strings, for example Income column can have values such as: High, Low, Medium, etc. How to know which unique strings there are in values of a column and how to replace those strings with a number in Excel?
To get the number of cells which have a specified string in it, use the following formulas:
=COUNTIF(B1:B4; "High") =COUNTIF(B1:B4; "Medium") =COUNTIF(B1:B4; "Low")
Adjust the range in the first argument to your needs.
The cell with one of these formulas will display the number of cells with the specified text.
To convert the text to a number, you can use either Search and Replace command (press Ctrl+H) or a temporary column with the formula:
B1 to your first cell and then drag auto-filler to copy the formula to other rows.
To find unique data, follow steps of Filter for unique values help article:
- Select the range of cells, or make sure the active cell is in a table.
- On the Data tab, in the Sort & Filter group, click Advanced.
- In the Advanced Filter dialog box, do one of the following:
- To filter the range of cells or table in place, click Filter the list, in-place.
- To copy the results of the filter to another location, do the following:
- Click Copy to another location.
- In the Copy to box, enter a cell reference.
- Select the Unique records only check box, and click OK.
Alternatively you can use the approach from the answer to Getting unique values in Excel by using formulas only, which is described in the following article: Create a unique distinct alphabetically sorted list, extracted from a column in excel
You'll need to do this in VBA:
- Declare a new dictionary
For..Nextover the cells you consider
- If the dictionary does not yet contain the value, add it along with a running number as key
For..Nextover the cells again
- Replace the values according the dictionary
I'm not having the time to write the code but I'm confident this points you towards a solution.