associate text string to a numeral

kmc

Board Regular
Joined
Sep 5, 2005
Messages
158
Office Version
  1. 2016
Platform
  1. Windows
I have an ongoing file in which every quarter I import a list of about 20 different names and I wish to have each different name be associated to a numerial in the next column.
For example: in col B I have the name John repeated 15 times, Sally 10x's, and Joe 3x's. I wish to have the number 1 match John, 2 match Sally, 3 Joe, and so on.

Currently, when I have my final list for the period, I sort the names list, then associate the predefined number to it's respective name. Is there a faster way? thanks
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It sounds like a Vlookup function would suit your purposes, unless I am under estimating the problem.
 
Upvote 0
It sounds like you want to "rank" each person based on the number of entries.

In a separate area, type "john", "sally" and "joe" (say in cells E1, E2 and E3)

In cell F1, type:
=COUNTIF(B:B,E1)

In cell G1:
=RANK(F1,F:F)

and copy down.

You can then use this as a lookup if you want to attach the numbers to each line.
 
Upvote 0
Hello KMC...

Why does each person recieve the number that you've given them ??

Is it a predetermined number ?
Is it due to the order on the sheet ?
Is is due to the number of entries that person has ie. the person with the most entries is number 1 ?
 
Upvote 0
To be more specific, the text strings are items purchased, I was using names facetiously. So col a=date, b=number c=item purchased, d= price.

b= is a predefined number to match a category of items purchased, say insurance is #4, building materials is 6, and so on.

The items purchased are repetitive and may have some variants in the latter part of the text string. So for example, building materials would include all items purchased from say, John’s building supply as the text string, but there would also be odd items that are not constant, ex. one item from Bill’s plumbing. Insurance could cover say Hartford, Met life, and worker’s comp, etc.

So what I have been doing is sort by the items purchased, and then manually associate a defined number for each category in col b. Then sort and subtotal the data by col b, to obtain a value for the total of the items purchased for the defined category.
 
Upvote 0
As I'm not as strong as others regarding formulas, I would create a UDF. However, if Aladin sees this thread, I'm sure he could probably figure out a solution for you...

wally
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,071
Members
453,593
Latest member
Mubashar Ali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top