Create unique client number (in increments of 1)

[ilsley_excel]

Hi,

I'm trying to generate a unique (arbitrary) client number for each client in a database, and ideally want to create a macro that does this for me. I've tried various formulas in the first instance, to no avail.

What I would typically have is a database that includes the client name, after which I would need to create a new column (column A) to produce a unique client number for each client, i.e.:


(Col A) (Col B)
ClientNo Client Name
1 Smith, John
1 Smith, John
1 Smith, John
1 Smith, John
1 Smith, John
1 Smith, John
2 Jones, Lisa
2 Jones, Lisa
2 Jones, Lisa
3 Anthony, Sarah
4 Baker, Joe
4 Baker, Joe
4 Baker, Joe



Any ideas?

Thank you in advance!

[Pete_UK]

Here's a formula you could use in A2:

=IF(B2="","",IF(B2=B1,A1,max(A$1:A1)+1))

Copy down as far as you need to (or beyond if you want to accommodate new data being added).

Hope this helps.

Pete

[:) Sixthsense :)]

Data Range

	A	B
1	ClientNo	Client Name
2	1	Smith, John
3	1	Smith, John
4	1	Smith, John
5	1	Smith, John
6	1	Smith, John
7	1	Smith, John
8	2	Jones, Lisa
9	2	Jones, Lisa
10	2	Jones, Lisa
11	3	Anthony, Sarah
12	4	Baker, Joe
13	4	Baker, Joe
14	4	Baker, Joe

Data Range

	A
2	=IF(B2="","",IF(COUNTIF($B$1:B1,$B2),INDEX($A$1:$A1,MATCH($B2,$B$1:$B1,0)),MAX($A$1:$A1)+1))

[ilsley_excel]

Data Range

	A	B
1	ClientNo	Client Name
2	1	Smith, John
3	1	Smith, John
4	1	Smith, John
5	1	Smith, John
6	1	Smith, John
7	1	Smith, John
8	2	Jones, Lisa
9	2	Jones, Lisa
10	2	Jones, Lisa
11	3	Anthony, Sarah
12	4	Baker, Joe
13	4	Baker, Joe
14	4	Baker, Joe

Data Range

	A
2	=IF(B2="","",IF(COUNTIF($B$1:B1,$B2),INDEX($A$1:$A1,MATCH($B2,$B$1:$B1,0)),MAX($A$1:$A1)+1))

Thank you! That's worked perfectly.

[ilsley_excel]

Here's a formula you could use in A2:

=IF(B2="","",IF(B2=B1,A1,max(A$1:A1)+1))

Copy down as far as you need to (or beyond if you want to accommodate new data being added).

Hope this helps.

Pete

Thanks for your reply; I appreciate it!