Assign number to groups of same numbers

[Guest]

Hi,

Here’s 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 they’re all not unique. Some
stores can have the same store number because they’re 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?

 

[pikapika13]

We would need more info such as: do you want this an automatic an
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. Thi
is the easiest way to get avoid duplicates. From the pivot, copy th
list into a new sheet. Number your list using autofill. 1 for firs
row, 2 for second, etc.

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

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

 

[Guest]

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.