Excel 2002: How to assign index numbers in block ?

[Guest]

Dear Sir,

I have the following worksheet :


A B C D
1 1 CX1245 3/22/2007 250
2 1 CX1246 4/27/2007 100
3 1 TOTAL 350
4 2 DX2241 6/21/2003 300
5 2 DX2242 4/7/2003 100
6 2 DX2246 8/12/2004 50
7 2 TOTAL 450
8 3 DX2632 6/21/2003 300
9 3 DX2644 6/24/2003 100
10 3 DX2322 4/17/2003 100
11 3 DX4500 8/12/2004 50
12 3 TOTAL 550
13 4 XV4177 8/9/2001 210
14 4 XV4178 9/8/2002 100
15 4 TOTAL 550


May I know what formula I must input at cell A1 and copy down to get the
index numbers as illustrated ?

How about if I want to start the number with 50, how the formula will look
like then ?


Thanks


Low

 

[Guest]

hi'
Assuming that your data starts at A1...enter the first number of your
sequence...say 50 per your example. then in A2 enter....
=sum(a1+1). copy down.

regards
FSt1

 

[Guest]

One way ..

Put in A1:
=IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL"),COUNTIF($C$1:C1,"TOTAL")+1)
Copy down

And if you want it to start from 50. adjust it like this in A1:
=IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL")+49,COUNTIF($C$1:C1,"TOTAL")+50)
Copy down

 

[Guest]

In A1: enter 1
In A2: =IF(C1="TOTAL",A1+1,A1)
copy down

 

[Guest]

Hello Max,

Many thanks for your formula.

It works perfectly well.


Kind Regards

Low

 

[Guest]

Hello Madam,

Many thanks for your formula.

It works perfectly well.


Kind Regards

Low

 

[Guest]

Dear Sir,

I tried your formula today, but I could not get what I wanted as illustrated.

The number are running in sequence for each row instead of running in blocks.

ie

1,1,1,2,2,2,3,3 and so on

I suppose the following formula could do the job.

In A1: enter 1
In A2: =IF(C1="TOTAL",A1+1,A1)
copy down

Thank you anyway for your prompt response.

Low

 

[Max]

Welcome, Low.
Think the earlier suggestion was overdone.
Go with the better one by TM.