increment a column based on information in another column

[dhemlinger]

Hi,

I have several spreadsheets that I need to increment a column each time
information in another column changes. Using example below, each time
program changes I need the count column to be incremented. I don't want
to use autofill as I have to do this every month and several sheets with
lots of rows.

Thanks for the help

Program Count
1100 1
1100 2
1100 3
1200 1
1200 2
1200
1200
1400
1400


--
dhemlinger

[JMB]

Assuming "Program" is in cell A1 and "Count" is in cell B1, I would enter the
following formula in cell B2 and copy it down.

=COUNTIF(A$2:A2,A2)


"dhemlinger" wrote:

>
> Hi,
>
> I have several spreadsheets that I need to increment a column each time
> information in another column changes. Using example below, each time
> program changes I need the count column to be incremented. I don't want
> to use autofill as I have to do this every month and several sheets with
> lots of rows.
>
> Thanks for the help
>
> Program Count
> 1100 1
> 1100 2
> 1100 3
> 1200 1
> 1200 2
> 1200
> 1200
> 1400
> 1400
>
>
> --
> dhemlinger
>

[Flintstone]

Hi

=IF(B1<>"",COUNTA($B$1:B20)&".","")

Copy this formula down from A1 to A20.

This formula works for data in column B, if data is deleted in B16 the line count in the A column will be numbered 1 through 19, skipping over the blank cell A16, that is the formula counts the non blank cells in the range B1:B20.

Notice the catenation in the formula { &"." }, you can remove this altogether or replace the period with whatever text you choose.

Matt