Creating Id
 

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

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

Creating Id
 

Use

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

--
HTH

Bob Phillips

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

"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:D
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

Creating Id
 

Hi Shail

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

Creating Id
 

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" 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

Creating Id
 

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" wrote in message
...
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

Creating Id
 

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" wrote in message
oups.com...
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" wrote in message
...
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:D
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

Creating Id
 

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

Creating Id
 

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 $2:D3,MAX(ROW($2:2)*(C$2:D
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 $2:D3,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" wrote in message
ups.com...
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