assign next serial number based on contents of another cell

[clint]

column A titled "serial number", column B titled "line item", column C titled
"cargo".
If column B contains 5 line items for the same cargo in column C, how do I
get column A to input the next serial number only after contents in Column C
has changed.

 

[Per Jessen]

Hi

Enter first serial number in A2, then insert this formula in A3 and copy
down. If cargo cell is empty, serial number cell will also be empty.

=IF(C3<>"",IF(C3<>C2,A2+1,A2),"")

Hopes this helps.
....
Per

 

[pshepard]

Hi Clint,

To increment one number in the serial number column when:

1. Cargo text field changes

OR

2. Above the current row there are 5 lines with the same cargo text as the
current row, AND those rows have the same serial number as the preceding row.

In cell A2 enter first serial number - i.e. 1001.

In cell A3 enter:

=IF(C2<>C3,A2+1,IF(COUNTIF(INDIRECT("C"&MATCH(A2,A:A,0)):C3,C3)>5,A2+1,A2))

Hope this helps.

Peggy