Creating Id

[shail]

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$23,MAX(ROW($2:2)*(C$22=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

 

[Roger Govier]

Hi Shail

Try
=TEXT(COUNTIF(C:C,C2),"000")

You could omit Col4 altogether and use
=C2&TEXT(COUNTIF(C:C,C2),"000")

 

[Bob Phillips]

Use

=TEXT(COUNTIF($C$1:C1,C2)+1,"0000")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Roger Govier]

Hi Shail

Ignore my response.
Bob has given you the correct solution.

 

[shail]

Hi Roger,

I have changed the way you told me but as soon as I enter the next
"Deogam" surname, the all ids of the same surname changed to the say
DEOG003.

Thanks,

Shail

 

[shail]

hi Bob,

Thanks it is working. Thanks to Roger too for your valuable time for
me. The formulae you gave me is so simple, whereas mine was too long
and complex. Do you have any idea, what exactly was wrong with my
formulae?

Thanks,

Shail

 

[Bob Phillips]

It's far too complex for the job, but to answer the question, it needs to be
array-entered and then it works.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[shail]

Hi again Bob,

Yes, I did it (array-entered, CTRL+SHIFT+ENTER), but it gave the
circular reference as soon as I entered the same surname just below as
above. Otherwise the formulae was working fine.

Thanks,

Shail

what does "HTH" mean? Is it "Heart to Heart" or "Hand to Hand" or
something else :-D

 

[Bob Phillips]

Sorry Shail, I didn't read it properly.

I think the problem is that you are using the MAX row number in the INDEX
function, bu you are getting the absolute row number within a function that
starts at roiw 2. If you started at row, no problem, otherwise you need to
allow for the start row

=IF(C3="","",IF(COUNTIF(C$2:C3,C3)>1,"000"&INDEX(C$23,MAX(ROW($2:2)*(C$2
2=C3))-ROW($C$2)+1,2)+1,IF(COUNTIF(C$2:C3,C3)=1,"0001","")))

I also would not test multi-column

=IF(C3="","",IF(COUNTIF(C$2:C3,C3)>1,"000"&INDEX(D$23,MAX(ROW($2:2)*(C$2:C
2=C3))-ROW($C$2)+1)+1,IF(COUNTIF(C$2:C3,C3)=1,"0001","")))


HTH is 'Hope that helps (helped)'

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)