# Solved Formula to give different number to different texts?

Microsoft Office 2013 professional produ...
February 7, 2015 at 01:34:41
Specs: Windows 8.1
 Hello allIf I have a column with texts in them (some of them quite large and beyond 255 charaters) and some of those texts are identical. Is there anyway to make a new column and in that give each of the different texts in the first column a different number. If its the same text, then it should have the same number.I would very much prefer a solution that doesnt involve VBA.Thanks in advance.

See More: Formula to give different number to different texts?

#1
February 7, 2015 at 11:27:40
✔ Best Answer
 Not sure what it is your doing, but here are two ways to get unique values.First and simplest:Select your column that contains the text stringsSelect the Data Tab on the ribbonSelect Remove DuplicatesYou should be left with a column of Unique values.Second way, that uses a formula: Make sure your text column has a Label, so your data looks like:``` A 1) Text Label 2) First Line of Text goes here 3) Second Line of Text goes here ```In Cell B2 enter the formula:=INDEX(\$A\$2:\$A\$10,MATCH(0,INDEX(COUNTIF(\$B\$1:B2,\$A\$2:\$A\$10),0,0),0))Without knowing how many rows you have, you will need to modify range in the formula.Drag down as many rows as needed.You will now have all the Unique text strings in column BUnsure if you plan on appending a number to the stringsor use an adjacent cell to number them.But this should get you started.MIKEmessage edited by mmcconaghy

Report •

#2
February 7, 2015 at 11:50:51
 Here is a third way to get your Unique values:Make sure your text column has a Label,so your data looks like:``` A 1) Text Label 2) First Line of Text goes here 3) Second Line of Text goes here ```Select your range of cells that contains data, you must include the Label,so you would select A1:A10 for instance.Select the Data Tab on the ribbonSelect Advance FilterIn the Advance Filter pop-up box,Check of the Copy to another location buttonThe List Range should already have your range of cells: A1:A10Click the Copy To box and select a location to copy your listto, like Cell C1Next, check the box that reads: Unique records onlyClick OKAnd you should have a unique list of your text in column CMIKEmessage edited by mmcconaghy

Report •

#3
February 8, 2015 at 11:12:58
 Thank you very much for the three solutions, I will give them a try and see whats happening. I will report back with my findings.

Report •

Related Solutions

#4
February 9, 2015 at 09:42:25
 re: "Is there anyway to make a new column and in that give each of the different texts in the first column a different number. If its the same text, then it should have the same number."Why not just use MATCH? If all you want is a unique number for each unique values and the same number for any strings that has a duplicate (or more), MATCH can do that for you. Granted, you may not end up with sequential numbers, but this method seems to return the results you asked for.With this data in A1:A6, enter this in B1 and drag it down to get the results shown below:=MATCH(A1,\$A\$1:\$A\$6,0)``` A B 1 Bob 1 2 Stu 2 3 Fred 3 4 Bob 1 5 Stu 2 6 Tom 6 ```

Report •