How to fill a column with sequential numbers when some rows need to be skipped

%3CLINGO-SUB%20id%3D%22lingo-sub-1674654%22%20slang%3D%22en-US%22%3EHow%20to%20fill%20a%20column%20with%20sequential%20numbers%20when%20some%20rows%20need%20to%20be%20skipped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674654%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20automate%20a%20process%20whereby%20I%20fill%20a%20column%20with%20a%20linear%20series%20of%20numbers%20with%20a%20step%20value%20of%201.%26nbsp%3B%20The%20challenge%20is%20that%20not%20all%20rows%20qualify%20to%20receive%20a%20number.%20(The%20rows%20represent%20orders%20and%20the%20number%20in%20the%20column%20becomes%20the%20box%20number%20for%20the%20ordered%20items.%26nbsp%3B%20Not%20all%20rows%20have%20orders.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20I%20use%20a%20simple%20formula%20which%20adds%201%20to%20the%20value%20in%20the%20cell%20above%20it.%20%5BC3%3DC2%2B1%5D%26nbsp%3B%20This%20works%20if%20the%20rows%20requiring%20a%20number%20assignment%20are%20contiguous%2C%20however%20some%20rows%20need%20to%20be%20skipped%20(the%20rows%20with%20no%20orders).%26nbsp%3B%20I%20could%20write%20an%20%22IF%22%20statement%20to%20have%20the%20formula%20look%20to%20see%20if%20there%20is%20an%20order%20in%20that%20row%2C%20but%20I%20do%20not%20know%20how%20to%20tell%20it%20to%20set%20the%20value%20to%201%20plus%20the%20last%20row%20with%20a%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1674654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1674942%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20fill%20a%20column%20with%20sequential%20numbers%20when%20some%20rows%20need%20to%20be%20skipped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796111%22%20target%3D%22_blank%22%3E%40RLevkoy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20you%20want%20to%20skip%20a%20row%20if%20column%20A%20is%20blank.%20In%20C3%3A%3C%2FP%3E%0A%3CP%3E%3DIF(A3%3D%22%22%2C%22%22%2CMAX(C%242%3AC2)%2B1)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to automate a process whereby I fill a column with a linear series of numbers with a step value of 1.  The challenge is that not all rows qualify to receive a number. (The rows represent orders and the number in the column becomes the box number for the ordered items.  Not all rows have orders.)

 

Currently, I use a simple formula which adds 1 to the value in the cell above it. [C3=C2+1]  This works if the rows requiring a number assignment are contiguous, however some rows need to be skipped (the rows with no orders).  I could write an "IF" statement to have the formula look to see if there is an order in that row, but I do not know how to tell it to set the value to 1 plus the last row with a value.

 

Any suggestions?

2 Replies
Highlighted

@RLevkoy 

Let's say that you want to skip a row if column A is blank. In C3:

=IF(A3="","",MAX(C$2:C2)+1)

Fill down.

Highlighted