Auto-increment in Excel, based on line value

Asked

Viewed 1,101 times

0

I have the following cells and columns:

inserir a descrição da imagem aqui

It needed to create an auto-increment, in column B, increasing only when the value of column A is different, starting with "1". Column C shows the desired result.

I tried with several "IF" but without success.

I appreciate your help.

excel

  • This is Stackoverflow in Portuguese. Please ask questions in Portuguese :)

  • 1

    You’re right, it’s the habit. Corrected. Thank you

  • No problem. Good luck!

1 answer

0


You can achieve this with only 3 SE functions.

Place the following function in the cell B2

=SE(A2=A1;SE(ÉNÚM(B1);B1;1);SE(ÉNÚM(B1);B1+1;1))

Explanation of logic:

  • The first SE will make the check if the cell that 1 house on the left is equal to the cell that is 1 house on the left and 1 on the top (In the case of B2 the comparison would be made in A2 and A1);
  • If true, then we enter the second SE function, it checks if the cell above the function has a numerical value, if YES, then the number is duplicated if the number 1 is not placed.
  • If false, then we enter the third SE function, it will also check if the cell above the function has a numerical value, if YES, then the number is incremented to 1, if the number 1 is not placed.

  • {=ROUND(SUM(1/COUNTIF($A$2:$A$10;$A10:$A$10));0)}

  • Excellent! It worked well. Thank you very much for the formula and the explanation.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.