4

I have text in column B. But there are some columns that are empty. There is no rule the empty raws are at random. This is what I have now:

Before

I want to have correlative numbers in column A. The problem is to detect the empty raws. That should be the result:

After

  • Do you want this as text is added? What if text is added in the middle, do you need the sequence of numbers to recalculate? If you could add a little more info about your process or how the data is added that would be helpful in finding a solution. In general, conditional formulas are your friend here. Cheers! – bjbk Aug 3 '17 at 16:57
  • @bjbk: it is ok if the numbers are created after the text column. The other cases would be a plus. Can you put an example of the most basic case, please? – Nrc Aug 4 '17 at 15:42
  • I would love to help, but if you could edit your question with more information. Such as, is the text simply just added to random rows? Is the empty row a result of inserting rows or deleting text? As asked, it really difficult to advise the best approach. It makes a difference in how the spreadsheet is managed. You may want to learn more about using functions and formulas. Cheers! – bjbk Aug 4 '17 at 20:33
  • 1
    I'm just curious - do you need to keep the blank rows? For example, in your second image Column A has been populated based on the content in Column B, but all the rows that are blank in Column B (e.g. rows 3, 6, 7 etc) are still there. Is this by design (e.g. some of the other columns have data in these rows) or can you delete these rows altogether? – Monomeeth Aug 5 '17 at 9:50
  • 1
    All good! I always get concerned when people have totally blank rows in a spreadsheet list, but obviously that's not the case here. :) – Monomeeth Aug 5 '17 at 23:09
3
  1. Enter a 1 in the cell A1.
  2. Insert the following formula into cell A2.

    IF(ISBLANK(B2),"",MAX(A$1:A1)+1)
    

    This formula checks the cell to the right to see if it is blank. If it is blank, the cell in the A column should be blank too. If not, the cell should be filled with the next integer, which equates to the maximum integer reached so far + 1.

  3. Fill the rest of the A column using this formula. Use the yellow fill handle so that the formula is adjusted correctly as it fills. The $ sign fixes the first cell of the range used by the MAX function.

  • It works very well. Clear and simple – Nrc Aug 5 '17 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .