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 all

If 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?

Report •


#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 strings
Select the Data Tab on the ribbon
Select Remove Duplicates

You 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 B

Unsure if you plan on appending a number to the strings
or use an adjacent cell to number them.

But this should get you started.

MIKE

http://www.skeptic.com/

message 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 ribbon
Select Advance Filter
In the Advance Filter pop-up box,
Check of the Copy to another location button
The List Range should already have your range of cells: A1:A10
Click the Copy To box and select a location to copy your list
to, like Cell C1

Next, check the box that reads: Unique records only
Click OK

And you should have a unique list of your text in column C


MIKE

http://www.skeptic.com/

message 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question