Creating Id
Hi Shail
Try
=TEXT(COUNTIF(C:C,C2),"000")
You could omit Col4 altogether and use
=C2&TEXT(COUNTIF(C:C,C2),"000")
--
Regards
Roger Govier
"shail" wrote in message
oups.com...
Hi friends,
I am in the middle of Creating Ids for the customers. I have somewhat
succeeded in it. I have the data as below:
Col1 Col2 Col3 Col4 Col5
Shail Deogam DEOG 0001 DEOG0001
Anupam Rathor RATH 0001 RATH0001
Shashi Deogam DEOG 0002 DEOG0002
1. The formulae I have used in the column 3 is
=UPPER(LEFT(B2,4))
2. The formulae I used at column 4 is
=IF(C3="","",IF(COUNTIF(C$2:C3,C3)1,"000"&INDEX(C $2:D3,MAX(ROW($2:2)*(C$2:D2=C3)),2)+1,IF(COUNTIF(C $2:C3,C3)=1,"0001","")))
where at the first row at the column (D2) I manually entered "0001"
and
at the rest of the rows I have entered the above formula and copied it
down.
3. At the column 5 it is the concatenation of column 3 and column 5
=C2&D2
Everything is working fine until I enter another surname of the same
as
just above. Say if I want to add "Ramesh Deogam" just below Shashi
Deogam, it gives me the "circular reference".
Any idea why this is happening and any idea to cure this thing.
Thanks,
Shail
|