Below is a sample spreadsheet. My data is in column A (below is only a sample...there may be 100 items in the first grouping followed by 2 blanks, and then 50 items in the next grouping)...In column B, I need to sequentially number each grouping of items starting with a new 1000 number.

Desired output:

 A            B
Apples      1000

Bananas     1001

Peaches     1002

Blank row

Blank row

Grapes      2000

Pears       2001

Blank row

Blank row

Kiwi        3000

Avocado     3001

Pineapple   3002

Try this: =IF(F2="","",IF(MAX($G$1:G1)=0,1000,IF(AND(F2<>"",G1<>""),G1+1,IF(AND(F2<>"",G1=""),FLOOR(MAX($G$1:G1)+1000,1000),""))))

This assumes your labels are in column F and your IDs in column G.

Here is what is going on in more detail:

=IF(F2="","",                        --If label is blank leave ID blank
  IF(MAX($G$1:G1)=0,1000,            --If no ID rows above then start at 1000
   IF(AND(F2<>"",G1<>""),G1+1,       --If Id row directly above then increment by 1
                                     --If new block add 1000 and round to nearest 1000
    ,""                              --Otherwise leave blank

And here's your output:

enter image description here

| improve this answer | |

In B1, simply type in 1000 as you know this value exists. The rest, we can use a worksheet formula!

Then in B2 add this formula

=IF(AND(A1<>"",A2<>""),B1+1,IF(AND(A1="",A2<>""),ROUNDUP(MAX($B$1:$B1 )+ 1,-3),""))

Drag it down! enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.