Correlative in discontinuous ranges excel

Question

Correlative in discontinuous ranges excel

Navigation

#1 by (0 votes)

0

I have a question. Do you know how I can assign numbers or correlative in discontinuous ranges?

the idea is to repeat it until space is detected, then continue but adding +1 for example: Start with a correlative 1, then when detecting the space and return to find data increase to 2 and so increasing until the end of the list. I'm using the formula

=SI(B2="";"";CONTAR.SI(B2;"<>")+MIN(0;C1))

the 2 that I show, is by way of example entered manually. the idea is to do it automatically because there are enough rows interspersed with spaces

excel

asked by Fabian Feriks 03.05.2018 в 22:42

source

1 answer

Mail by mailgun sql server Medoo - Include MySQL query directly

score 0 · Answer 1

I think I understand your question and I addressed it in the following way

The formula in D2 would be as follows:

=+SI(                             -- primer condicional si

      B2="";                      -- si la celda B2 está vacía
         "";                      -- no asignar nada a la celda D2 

      SI(                         -- caso contrario
          Y(ESNUMERO(D1);D1<>""); -- si D1 es número y no está vació
                                  -- (esto último por los encabezados iniciales que
                                      puedas tener)

           D1;                    -- que la celda D2 sea igual a D1

           MAX($D$1:D1)+1         -- caso contrario tomar el valor máximo desde
                                  -- la celda D1 ($D$1) será fija siempre hasta la celda
                                  -- D n-1 es decir si estoy en D2 será D1

         )                        -- cerramos caso contrario
      )                           -- cerramos primer condicional si

Without comments like this would be:

=+SI(B2="";"";SI(Y(ESNUMERO(D1);D1<>"");D1;MAX($D$1:D1)+1))

Please keep in mind that I did it in excel 2010 for mac, if there is a difference between the formulas by the operating system and / or version of the excel you use, leave it in the comments please, because in the macOS Sierra, There is not all the office services provided by MS and this includes VBA-Macros.