Assign number to groups of same numbers
 

Hi,

Heres the situation.

In Excel I have:

1st column: Thousands of store numbers.
2nd column: I manually created something called €œMaster Corp ID€�

The Problem:
1st Column has approx. 29,500 store numbers but theyre all not unique. Some
stores can have the same store number because theyre all the same type of
store€¦ i.e Circuit City, Best Buy, also, not all Circuit, Best Buy will not
have the same unique number due to type of store it is/area and et cetera.
In-addition, I also have a ton of mom and pop shops with their unique store
numbers.

What I Need Please:
I need a way to in column 2 to automatically assign numbers for each group
of store numbers so for example:

Store Number Master --- Corp ID
2205 1
2205 1
2205 1
2205 1
3566 2
4523 3
5545 4
5545 4
6001 5
6001 5
6001 5

Can someone please help me?

Assign number to groups of same numbers
 

We would need more info such as: do you want this an automatic and
ongoing agenda or a one time deal?

Here's the semi-long way for a one time deal:

Select all of column A including Header and create a pivot table. This
is the easiest way to get avoid duplicates. From the pivot, copy the
list into a new sheet. Number your list using autofill. 1 for first
row, 2 for second, etc.

Now go back to your column B and put in a VLOOKUP function based off of
this new sheet.

If you're looking for something more dynamic, you're gonna need a macro
I believe.


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570868

Assign number to groups of same numbers
 

If the first columns numbers are grouped together, assuming column 1 is 'A'
and column 2 is 'B' and Row 1 is your column headings, you would need to type
a 1 in 'B2' to give a starting reference. Then place this formula in 'B3' and
copy it all the way down your spreadsheet:
=If(A2=A3,B2+1,B2)

This will compare column 1's value to the cell above it, if it's the same it
will leave the value in column 2 the same as it was. If the value is
different it will increase the value in column 2 by 1.

Hope this helps.

"mike" wrote:

Hi,

Heres the situation.

In Excel I have:

1st column: Thousands of store numbers.
2nd column: I manually created something called €œMaster Corp ID€�

The Problem:
1st Column has approx. 29,500 store numbers but theyre all not unique. Some
stores can have the same store number because theyre all the same type of
store€¦ i.e Circuit City, Best Buy, also, not all Circuit, Best Buy will not
have the same unique number due to type of store it is/area and et cetera.
In-addition, I also have a ton of mom and pop shops with their unique store
numbers.

What I Need Please:
I need a way to in column 2 to automatically assign numbers for each group
of store numbers so for example:

Store Number Master --- Corp ID
2205 1
2205 1
2205 1
2205 1
3566 2
4523 3
5545 4
5545 4
6001 5
6001 5
6001 5

Can someone please help me?