text in col d to numeral in col c

kmc

Board Regular
Joined
Sep 5, 2005
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
I have a list of repetitive words in col d that I would like to have associated with a constant numeral in col c.
example
col c col d
1 jones
1 jones
1 jones
2 smith
2 smith

The number of occurance for each similar text, however is variable. I may have 5 or 25 rows of the same word.

Currently, I add the numerals in col c manually, how can I automate this task? I've attempted using vlookup, but my since I am not using col a, I am not sure how to run it. thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
Assuming that D2:D30 contains the data, try...

C2, copied down:

=IF(D2<>D1,SUM(C1,1),C1)

Hope this helps!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If the names are in D2 and downward, perhaps in C2:

Code:
=SUM(C1,D2<>D1)

Copy down.
Book1
CDEF
1numbername
21jones
31jones
41jones
52smith
62smith
73white
83white
94brown
Sheet1
 

kmc

Board Regular
Joined
Sep 5, 2005
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
Thank you both,
Domemic, your solution seems to be on the right track, with just a bit of tweeking. My list of items in col d does not correspond with the same alphabetic sequence in the solution. The list is a credit/debit sumamry of which I wish to catagorize based on a 25 catagory list I have. So in col d I have the alphbatized list, and in col c I want a constant numeral to match each occurance of the item.
So for each occurance of "jones" or "harris" or "thomas" in d I want the number "2" to be reflected in col c. Each "smith" or "jacks"would reflect a "4", and so on. Another way of looking at this is I have the occurances and subcategories in col d and I wish to group them into larger catagories in col c.

thank you
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

OK,
I have made a table in H1:I5

Formula in C2:
=IF(ISNUMBER(MATCH(D2,$H$1:$H$5,0)),VLOOKUP(D2,$H$1:$I$5,2,0),"")

Copy down.
Book1
CDEFGHI
1numbernamejones1
21jonesharris1
34smiththomas1
41harrissmith4
51thomasjacks4
Sheet1
 

kmc

Board Regular
Joined
Sep 5, 2005
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
thanks Hot pepper for the attempt. I attempted your solution and got nothing in H1:I15. I not very savy with programming, but it would seem that I need some sort of fill in the blank "IF...then" as you do not know my categories. Does that help? Perhaps I am not being clear enough.
pls advise.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
...I need some sort of fill in the blank "IF...then" as you do not know my categories....

It seems to me that HOTPEPPER has done that exactly. He's provided a table, H1:I5, where he lists the corresponding values for each name. Then, with your list of names in Column D, starting at D2, his formula is entered in C2 and copied down. The formula will return the corresponding values for each name in Column C according to the table in H1:I5.

Hope this helps!
 

kmc

Board Regular
Joined
Sep 5, 2005
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
I only needed to read it over about 10 times. Hotpepper my apologies for not following your example. Domenic, thank you for your clarifiying info. Success has been achieved!
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

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
Top