Auto-populate numbers based on cell value. No duplicates.

[kevindorward_dfsdanf]

Example sheet

I am trying to auto-populate the 'Number' column on the general sheet with values from the 'Numbers' sheet.
If the role code = IC then choose cell B2 on the numbers sheet. But if the role code is IC again then move along one cell etc so the number column always has a unique value.
If the role code = VS then it should start at 1 and continue +1 if it appears again, +2 if it appears a 3rd time etc so all role codes have an associated value that is equal to the number of times they have appeared e.g

IC - 1
IC - 2
IC - 3

or

IC - 1
VS - 1
VS - 2

 

[Toadstool]

Does this do what you want?

=INDEX(Numbers!$A$1:$ZZ$999,MATCH([@[Role Code]],Table2[Code],0),COUNTIF($B$2:B2,[@[Role Code]])+1)

 

[kevindorward_dfsdanf]

Does this do what you want?

=INDEX(Numbers!$A$1:$ZZ$999,MATCH([@[Role Code]],Table2[Code],0),COUNTIF($B$2:B2,[@[Role Code]])+1)

That does Thank you very much.
Is it then possible to further that so for example if there were other criteria to match?

So if there were another column called 'Business Unit' for example with a two letter value {BB, AC,GG}, could the number changed based on both of these criteria? So it would be unique to both criteria?

e.g.

IC - BB - 1
CS - GG - 1
IC - AC - 1
IC - AC - 2
IC - BB - 2
CS - AC - 1

 

[Toadstool]

So would the Numbers tab change format?
Does it have to use Numbers tab, what appears to be just ascending numbers in Numbers, or can I just make it the next integer?

 

[kevindorward_dfsdanf]

I think as long as each combination of role and business unit increases by one that would be fine

 

[Toadstool]

I think as long as each combination of role and business unit increases by one that would be fine

Then you can add a column to concatenate the two unique keys with a separation you won't use as a key and do a COUNTIF

Book1
	A	B	C	D
1	IC	BB	IC##BB	1
2	CS	GG	CS##GG	1
3	IC	AC	IC##AC	1
4	IC	AC	IC##AC	2
5	IC	BB	IC##BB	2
6	CS	AC	CS##AC	1
Sheet1

Cell Formulas
Range	Formula
C1:C6	C1	=A1&"##"&B1
D1:D6	D1	=IF(C1="","",COUNTIF($C$1:$C1,A1&"##"&B1))

 

[kevindorward_dfsdanf]

Perfect. That works a treat. I appreciate all the help, thank you very much