4 responses

  1. Rick Rothstein (MVP – Excel)
    April 6, 2015

    First off, you show your formula starting in cell B3… you need to adjust it so that it starts in cell B2, otherwise you will not show a 1 in cell B2 if cell A2 differs from cell A3.

    Second, here is a much simpler, still array-entered, formula, placed in cell B2 and copied down, that appears to produce the same output as your formula…

    =IF(A3<>A4,ROW()-MAX(IF(A$1:A2<>A3,ROW(A$1:A2))),””)

    Reply

    • Rick Rothstein (MVP – Excel)
      April 6, 2015

      Sorry, I posted the wrong formula, plus it looks like the comment processor ate my less than greater than symbol. Here is the correct array-entered formula, rearrange to eliminate the display problem…

      =IF(A2=A3,””,ROW()-MAX(IF(A$1:A1<>A2,ROW(A$1:A1))))

      Reply

    • Rick Rothstein (MVP – Excel)
      April 6, 2015

      D@MN! I missed that the comment processor ate a second less than, greater than symbols in my formula. Here now is the correct [b]array-entered[/b] formula (rearranged so that there are no less than, greater than symbols at all)..

      =IF(A2=A3,””,ROW()-MAX(IF(A$1:A1=A2,,ROW(A$1:A1))))

      Reply

    • Oscar
      April 6, 2015

      Rick Rothstein (MVP – Excel),

      Your formulas work fine, thanks. So much better and smaller than mine.

      I am sorry for wordpress eating your less/greater than signs, I try to edit your comments as soon as I can.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top