Excel HELP!! Simple question... really

[strangr363]

Hi. Before I jump out of a window, can someone please help me figure this out:

I have a column of Company Names that I need to assign a unique ID number to. Here is the rub, since the Company Names are attached to the Company Employees, there are many duplicates...

Example:

<TABLE style="WIDTH: 479pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=638 border=0 x:str><COLGROUP><COL style="WIDTH: 242pt; mso-width-source: userset; mso-width-alt: 11776" width=322><COL style="WIDTH: 237pt; mso-width-source: userset; mso-width-alt: 11556" width=316><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 242pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=322 height=17>COMPANY NAME</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 237pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=316>COMPANY ID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Youville Hospital</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Youville Hospital</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Youville Hospital</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Yonkers General Hospital</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Wyoming Vly Hlth Care System</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Wyoming Vly Hlth Care System</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Wyoming Vly Hlth Care System</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Wyeth</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Wyeth</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

So for Youville Hospital, I would need the ID to be the same for all three, then for Yonkers, another unique value, etc etc...

Col A has about 10,000 Company Names...

I do NOT want to fuss with VBA... I am 'better' in Excel.

T H A N K Y O U

 

[PaddyD]

hi - welcome to the board!

Many way to do this - flaovour will depend on how your problem's likely to change over time. If this a one-off deal I'd do the following:

1) select the name column, go to data | filter | advanced filter. select 'unique items' and 'copy to another location' options. this will give you a list of all the companies

2) generate a number for each of the companies. simplest way is to put 1 for the first, 2 for the second, select the 2 cells & copy down - excel will fill in the series.

3) use a vlookup() formula to match the company number to the company name in the orignial records

 

[Barry Katcher]

First, do Data>Filter>Advanced Filter>Unique records only on your list of all the Hospital names. Copy that filtered data to another place on the worksheet or on another sheet.

Then, assign unique ID numbers to that filtered list, and use it as a Lookup List for a VLOOKUP formula. See the formula in B1 below.

Book1
	A	B	C	D
1	Hospital
2	Youville Hospital	11112
3	Youville Hospital	11112
4	Youville Hospital	11112
5	Yonkers General Hospital	11113
6	Wyoming Vly Hlth Care System	11114
7	Wyoming Vly Hlth Care System	11114
8	Wyoming Vly Hlth Care System	11114
9	Wyeth	11115
10	Wyeth	11115
11
12
13
14
15
16	Lookup List
17	Youville Hospital	11112
18	Yonkers General Hospital	11113
19	Wyoming Vly Hlth Care System	11114
20	Wyeth	11115
Sheet3

Edit: Hey, Paddy - Great Mind Stink Alike

 

[strangr363]

WHOOO HOOOOO.... I am in BUSINESS!!!!

THANK YOU! You guys R O C K!

James

 

[Kaps_mr2]

or look at :-

http://www.automateexcel.com/2008/10/20/excel-creating-unique-identifiers/

Thanks

kaps