Assign Unique Account Numbers per Criteria

[austinandreikurt]

Hi,
I want to assign in Column A a unique series of account number per company name in Column B. I'm thinking of 6series account numbers that will be assigned per unique company then the numbers will be assigned the same for any duplicates. Note that the series will be based on the order they first appear. In this sample, ignore the numbering of the companies, it doesn't affect the order of series. Below is the sample I want as a result in Account Number. Thanks in advance!

Account Number	Company A
600001	Company 1
600002	Company 2
600003	Company 3
600002	Company 2
600004	Company 5
600001	Company 1
600005	Company 7
600002	Company 2
600006	Company 9
600007	Company 4
600008	Company 11
600009	Company 12
600006	Company 6
600007	Company 4
600010	Company 10
600011	Company 16
600012	Company 17

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1	Account Number	Company A
2	600001	Company 1
3	600002	Company 2
4	600003	Company 3
5	600002	Company 2
6	600004	Company 5
7	600001	Company 1
8	600005	Company 7
9	600002	Company 2
10	600006	Company 9
11	600007	Company 4
12	600008	Company 11
13	600009	Company 12
14	600010	Company 6
15	600007	Company 4
16	600011	Company 10
17	600012	Company 16
18	600013	Company 17
Data

Cell Formulas
Range	Formula
A3:A18	A3	=IFERROR(INDEX(A$2:A2,MATCH(B3,B$2:B2,0)),MAX(A$2:A2)+1)

 

[austinandreikurt]

Ow great. That was fast and my I didn't find the error in my samples but with the use of formula, it is now corrected. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.