1

I have a column with values that repeat an arbitrary number of times before the next block begins. Some of these blocks, however, contain no number at all. While every other block is unique, these are not. That is, each other block contains numbers that occur in no other block. To fix this I would like to find a way to tell Excel to count from 1 to n and input that number in the blank spaces of the column for each blank 'block'. Is this something that's doable without VBA?

Example:

123          
123         
123           
123
123





112
112
112
112
111
111
111
111





312
312
312

The idea would be to place multiple 1s in the first space, multiple 2s in the second space, and so on.

  • give us a samlpe of data in the column, just couple of blocks, with those unique values – sgp667 Oct 16 '14 at 19:44
  • Since you can't push data into other cells using formulas, you're probably out of luck without a macro. – Mr. Mascaro Oct 16 '14 at 19:47
  • @jbarker2160 Thanks, any suggestions on how such a macro would work? – Max Power Oct 16 '14 at 20:00
  • I'm still not clear about what you want. Do you want each group of results to be the same number of rows? How do I tell where a group starts and ends (since 112 and 111 can be in the same group.) – Mr. Mascaro Oct 16 '14 at 20:02
  • @jbarker2160 The way I see it the macro would have to notice that the next cell is blank, and fill until the cell is non-blank. It would then have to keep going, notice the cell is blank again, and continue the process. Each time it notices a blank cell the count would increase by 1 so that the number is unique. I'm not skilled enough to create such a macro in vba unfortunately. – Max Power Oct 16 '14 at 20:10
0

As has been stated in the comments, formulas (worksheet functions) can’t modify cells.  But if you’re content with displaying the values you want, this is easy.  Assuming that I understand what you want.

If your data are in Column A, starting in Row 1, then enter

=IF(NOT(ISBLANK(A1)), A1, 1)

into cell B1, and

=IF(NOT(ISBLANK(A2)), A2, IF(NOT(ISBLANK(A1)), 1, B1+1))

into cell B2, and drag this down to cover the data in Column A (Row 26, for your sample data) or beyond (if you want).  This gets results like this:

                                                                data as requested

Explanation:

  • The first row is different because there is no previous row to look at.  Here, if the value in A1 is not blank, we display it; otherwise, we display 1.  Stated more generally,

    If the value in the cell to the left is not blank, display it; otherwise, we display a generated, sequentially inserted number.

  • Cell B2 follows this same general pattern: if the value in A2 (the cell to the left) is not blank, display it; otherwise, display a generated number.  The “else” clause (the “otherwise” formula) is

    IF(NOT(ISBLANK(A1)), 1, B1+1)
    

    That checks if the value in the cell to the left and one row up is not blank – i.e., if this row is the first of a block of blank rows.  If this row is the first of a block of blank rows, we display a 1.  Otherwise, we take the value from the cell above this one (B1) and add 1 to it (i.e., count).

  • This looks great, the only issue is that it repeats the count at each blank. This results in duplicate numbers unfortunately. Is it possible to have it keep counting up? – Max Power Oct 16 '14 at 22:20
  • So, are you saying that you want Rows 19-23 to contain 6, 7, 8, 9, 10? – G-Man Says 'Reinstate Monica' Oct 16 '14 at 22:21
  • Yes, that's correct. I resolved this issue by using INDIRECT to base the values on the associated row value. – Max Power Oct 28 '14 at 14:43

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.