Serial nos for unique records
 

Hi,
Have a report with the following data

Insurance Patient Name Claim #
EINSURANCE CO. A 123
EINSURANCE CO. A 234
ABC INSURANCE B 245
G INSURANCE C 456
A INSURANCE B 567
EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


What I require is a serial no based on unique patients so the above report should ideally read... is there a formula i can use?

Sr No Insurance Patient Name Claim #
1 EINSURANCE CO. A 123
EINSURANCE CO. A 234
2 ABC INSURANCE B 245
3 G INSURANCE C 456
A INSURANCE B 567
4 EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


Lynne

Serial nos for unique records
 

In E2: enter 1
In E3: =IF(COUNTIF($A$2:A3,A3)=1,MAX($E$2,E2)+1,"")
Copy down as far as required

"Lynne" wrote:

Hi,
Have a report with the following data

Insurance Patient Name Claim #
EINSURANCE CO. A 123
EINSURANCE CO. A 234
ABC INSURANCE B 245
G INSURANCE C 456
A INSURANCE B 567
EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


What I require is a serial no based on unique patients so the above report should ideally read... is there a formula i can use?

Sr No Insurance Patient Name Claim #
1 EINSURANCE CO. A 123
EINSURANCE CO. A 234
2 ABC INSURANCE B 245
3 G INSURANCE C 456
A INSURANCE B 567
4 EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


Lynne

Serial nos for unique records
 

Thanks... that worked well. Just changed the MAX funtion to include a
range.. that may have be a typo error.

Regards,

"Teethless mama" wrote in message
...
In E2: enter 1
In E3: =IF(COUNTIF($A$2:A3,A3)=1,MAX($E$2,E2)+1,"")
Copy down as far as required

"Lynne" wrote:

Hi,
Have a report with the following data

Insurance Patient Name Claim #
EINSURANCE CO. A 123
EINSURANCE CO. A 234
ABC INSURANCE B 245
G INSURANCE C 456
A INSURANCE B 567
EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


What I require is a serial no based on unique patients so the above
report should ideally read... is there a formula i can use?

Sr No Insurance Patient Name Claim #
1 EINSURANCE CO. A 123
EINSURANCE CO. A 234
2 ABC INSURANCE B 245
3 G INSURANCE C 456
A INSURANCE B 567
4 EINSURANCE CO. D 678
ABC INSURANCE B 789
ABC INSURANCE B 890
EINSURANCE CO. A 999


Lynne