Sequential numbering

[apgmin]

I need the following help urgently, Please see if you can tell me what to do.

I have 3 codes for 3 sales men, A,B and C

Now everytime that they take expense reimbursement payments, I number the voucher as 1,2,3 and so on. Now the problem is the number here does not reflect the nos. of voucher the sales man has taken in the reference. ( Like salesman A may have 2 nos. of vouchers against him and salesman B may have 1 nos. of vouchers against him )

Like say voucher SL. No. 1 was of Sales man A, voucher No. SL. 2 was of Sales man B and voucher SL. No. 3 was of Sales man A again,

In the excel sheet the serial nos. shows 1,2,3 and so on.

Now If I make a pivot table then I see that Sales man A has 2 vouchers against him ( voucher SL. no. 1 & 3)

I want to make a reference column which will show against voucher SL. no. 1 a number 1 & against Voucher SL No. 3 a number 2, this will denote that it is the second voucher of the sales man A

I did index and then countif, works fine (reference column against voucher SL. no. 3 shows a number 2, To indicate that it is the second voucher of sales man A)

but when I enter voucher No. 3 the reference column against voucher SL. no. 1 also changes from number 1 to number 2. I DO NOT WANT THIS TO HAPPEN

 

[Rick Rothstein]

Assuming your salesmen's names are in Column A starting in Row 2 (I'm assuming Row 1 is a header), put this in Row 2 of whatever column you are using for this index and copy it down to the end of your data...

=COUNTIF(A$2:A2,A2)

 

[apgmin]

sir the same thing is happening, the earlier number also changes if the count changes

 

[apgmin]

sir, It is not working

When the 3rd count changes to 2 the first count also changes to 2 instead of remaining as 1

 

[Rick Rothstein]

sir, It is not working

When the 3rd count changes to 2 the first count also changes to 2 instead of remaining as 1

I have a feeling I am not understanding your layout. Can you poste an example set of data (maybe 10 rows or so) showing the salesman's name, the vouchers and, most important, the numbers you would like to see for each row.

 

[apgmin]

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Voucher SL No.[/TD]
[TD]code[/TD]
[TD]sr.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LIG[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DHA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DHA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DUR[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LIG[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]


Sir see the above example

there are 2 LIG and 2 DHA, Now suppose I put another LIG in cell B6, then the count will change to 3 for voucher No. 1 and 5 also ( Like it changed to 2 when i made the entry of voucher No. 5 ), I do not want this to happen, voucher No. 1 should have LIG sr. 1 when I put another voucher later the count only for that voucher should change to 2 and not the earlier vouchers.

SO on and so forth

 

[MAHMOUD-LEE]

You can use this formula
=COUNTIF($B$2:$B$6;B2)

 

[apgmin]

it saws wrong formula

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Voucher SL No.[/TD]
[TD="class: xl65, width: 64"]code[/TD]
[TD="class: xl65, width: 64"] sr.[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]LIG[/TD]
[TD="class: xl66"] =COUNTIF($B$2:$B$6;B2)[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]2[/TD]
[TD="class: xl65, width: 64"]DHA[/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]3[/TD]
[TD="class: xl65, width: 64"]DHA[/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]4[/TD]
[TD="class: xl65, width: 64"]DUR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]5[/TD]
[TD="class: xl65, width: 64"]LIG[/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

[TABLE="width: 192"]
<tbody>[TR]
[TD]Voucher SL No.[/TD]
[TD]code[/TD]
[TD]sr.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LIG[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DHA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DHA[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DUR[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LIG[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Sir see the above example

there are 2 LIG and 2 DHA, Now suppose I put another LIG in cell B6, then the count will change to 3 for voucher No. 1 and 5 also ( Like it changed to 2 when i made the entry of voucher No. 5 ), I do not want this to happen, voucher No. 1 should have LIG sr. 1 when I put another voucher later the count only for that voucher should change to 2 and not the earlier vouchers.

SO on and so forth

That is what my formula does, you just have to point it at the correct column. If your codes are in Column B, then try my formula this way...

=COUNTIF(B$2:B2,B2)

Make sure you include the $ sign where I show it.

 

[apgmin]

that is what my formula does, you just have to point it at the correct column. If your codes are in column b, then try my formula this way...

=countif(b$2:b2,b2)

make sure you include the $ sign where i show it.

thanks you a million sir, it works