Assigning Numeric Identifiers

[ajcormac]

Hi there,

I have a column that contains non-numeric values (in some cases, repeated) - for example, software brands. Am I curious if there is a quick (1-step) way to assign numbers to each value in the column such that common elements will be assigned the same number. In other words, replace each value in a column with a numeric identifier.

Bonus if I can extend this to multiple columns.


Any thoughts on tackling this problem? My current method does not seem practical for performing this task multiple times.

 

[Jerry Sullivan]

Hi ajcomac, Are you wanting a formula or VBA approach?

For most purposes like this, one would create a table of unique values then use a lookup to assign the IDs. That can be done pretty quickly either manually or with VBA and provides you with a key to restore the non-numeric items if needed.

I don't think there's a formula-based way to replace the items with numeric IDs in 1-step. You could use a formula like this, then copy-paste special values to replace the items with numeric IDs.

Excel Workbook
	A	B
1	ID	Item
2	1	apple
3	2	banana
4	3	cherry
5	4	grape
6	5	orange
7	6	tangerine
8	7	mango
9	2	banana
10	3	cherry
11	1	apple
Sheet

To extend this to multiple columns, add a helper column that concatenates the fields into a key string....

Excel Workbook
	A	B	C	D	E
1	ID	Key String	Source	Size	Item
2	1	california|large|apple	california	large	apple
3	2	florida|small|banana	florida	small	banana
4	3	georgia|large|cherry	georgia	large	cherry
5	4	california|small|grape	california	small	grape
6	5	florida|large|orange	florida	large	orange
7	6	georgia|small|tangerine	georgia	small	tangerine
8	7	california|large|mango	california	large	mango
9	2	florida|small|banana	florida	small	banana
10	3	georgia|large|cherry	georgia	large	cherry
11	8	california|small|apple	california	small	apple
Sheet

 

[ajcormac]

Hi Jerry,

Your solution is working excellently!
Thank you for the detailed response on how to tackle my problem - it is much appreciated.

 

[simpleguy]

Hello Jerry,

I have a question. Why you use so many formulas if you can get the same result using just MATCH? I don't understand.

Thank you

 

[Jerry Sullivan]

Hi simpleguy and welcome!

You are right that a simple match formula would be better for the dataset in my example.

The formula I suggested was intended to provide sequential numbers which a single match expression wouldn't do if there are items in column B that have their first instance after other item(s) in column B have had duplicates.

If we add a new item to the list, you'll see the numeric identifier skips the numbers 8-10...

Excel 2013
	A	B	C	D
1	ID	Item	ID	Item
2	1	apple	1	apple
3	2	banana	2	banana
4	3	cherry	3	cherry
5	4	grape	4	grape
6	5	orange	5	orange
7	6	tangerine	6	tangerine
8	7	mango	7	mango
9	2	banana	2	banana
10	3	cherry	3	cherry
11	1	apple	1	apple
12	8	new item	11	new item
Sheet1

Cell Formulas
Range	Formula
A2	=IF(COUNTIF(B$2:B2,B2)=1,MAX(A$1:A1)+1,INDEX(A$2:A2,MATCH(B2,B$2:B2,0)))
C2	=MATCH(D2,D$2:D$100,0)

The OP didn't specify that the numeric identifiers needed to be consecutive, and yes, it would be much better to use a simple match formula if that wasn't a requirement.

 

[simpleguy]

Thank you a lot Jerry, very good explanation.
I'm a beginner in Excel so this example was tricky to understood.