| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help creating invoice in excel based on data and | Excel Discussion (Misc queries) | |||
| Help needed - creating invoice from packing list (both in excel) | Excel Discussion (Misc queries) | |||
| Help needed - creating invoice from packing list (both in excel) | Excel Discussion (Misc queries) | |||
| Creating username and passwords | Excel Worksheet Functions | |||
| Creating custom list with a comma in it | Excel Discussion (Misc queries) | |||
Linear Mode
