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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Use

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

--
HTH

Bob Phillips

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

"shail" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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$2
2=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
>

Hi Shail

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

--
Regards

Roger Govier


"Roger Govier" <(E-Mail Removed)> wrote in message
news:%235hVVc$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

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


Roger Govier wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >

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


Roger Govier wrote:
> Hi Shail
>
> Ignore my response.
> Bob has given you the correct solution.
>
> --
> Regards
>
> Roger Govier
>
>
> "Roger Govier" <(E-Mail Removed)> wrote in message
> news:%235hVVc$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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
> >>
> >
> >

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> Roger Govier wrote:
> > Hi Shail
> >
> > Ignore my response.
> > Bob has given you the correct solution.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Roger Govier" <(E-Mail Removed)> wrote in message
> > news:%235hVVc$(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> 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$2
2=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
> > >>
> > >
> > >
>

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 wrote:
> 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
>
>

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)

"shail" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
> > 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
> >
> >
>