Unir dos rangos en uno solo

[flopez35]

Necesito saber como puedo unir dos o más rangos uno a continuación del otro utilizando fórmulas, he buscado en internet y no he encontrado una forma de hacerlo

[#2 · Accepted solution]

@flopez35 

Using Excel 365 the content of the two ranges may be combined to give an array

= LET(
    m, ROWS(range1),
    n, ROWS(range2),
    k, SEQUENCE(m+n),
    IF(k<=m, INDEX(range1,k), INDEX(range2,k-m))
  )

[#3]

@Peter Bartholomew 

Muchas gracias por la solución, 

No obstante, ¿se podría aplicar para más de dos rangos (3 ó 4 rangos)?

gracias

[#4]

@PakitoGARRIDO 

It is still quite possible to use SEQUENCE and modify the formula to pickup data from the correct array but newer array shaping functions may offer a better solution.  For example

= VSTACK(range1, range2, range3, range4)

If the number of ranges is variable, but they lie within an overall containing range then

= TOCOL(containingRange,1,TRUE)

would be preferable.

[#5]

@Peter Bartholomew 

Gracias de nuevo, efectivamente he leído sobre esas funciones, la cuestión es que no dispongo de 365 y por tanto no las puedo utilizar.

saludos

[#6]

@PakitoGARRIDO 

Bad luck!  I would not consider using legacy versions of any spreadsheet software; they are terrible.

I think

= IFS(
    k <= n₁,           INDEX(range1, k),
    k <= n₁ + n₂,      INDEX(range2, k - n₁),
    k <= n₁ + n₂ + n₃, INDEX(range3, k - n₁ - n₂),
    TRUE,              INDEX(range4, k - n₁ - n₂ - n₃)
  )

may work on a non-dynamic array version of Excel (older versions require nested IF rather than IFS) but I have no way of testing it.  The intention is that it should work either as a CSE array formula or but using relative referencing.