Assign a unique number based on text in two other cells?

[daithi_ie]

I, apologies in advance if this has been answered before but I haven't been able to find a solution to my problem.

I'm trying to assign a unique number to a cell-based on data in two other cells. I have a competition coming up and we have loads of unique numbers to assign, over 500, and they have to go to a unique combination.

For example below, each unique forename (C1) and surname (D2) combination are assigned a unique number (B1).

Is there a formula I can use to do this? I have some knowledge of excel but am still learning loads from it! Hope you can help? TIA

Time	Unique No	Forename	Surname	Club	Competition
09:00	1	Mary	Adams	Evergreen	Grade 1
13:05	1	Mary	Adams	Evergreen	Grade 2
09:10	2	John	Adams	Bluegrass	Grade 1
09:15	3	John	Brown	Cederwood	Grade 1
13:20	3	John	Brown	Cederwood	Grade 2
09:25	4	Bobby	Clover	Seapoint	Grade 1

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(C$2:C2&"|"&D$2:D2))

 

[daithi_ie]

Hi, thanks for that but if I put your formula in B2 and drag it down it assigns 1 to all cells. I need them to have a unique number?

 

[Fluff]

It shouldn't do that, is calculation set to manual?

 

[daithi_ie]

Just tried it again and it worked a treat - you've saved me so much time. Really appreciated!!

Would this work in Google Sheets too?

 

[Fluff]

I've no idea, best way to find out is to try it.