SOLVED

# Unir dos rangos en uno solo

Copper Contributor

# Unir dos rangos en uno solo

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

5 Replies
best response confirmed by flopez35 (Copper Contributor)
Solution

# Re: Unir dos rangos en uno solo

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))
)``````

# Re: Unir dos rangos en uno solo

Muchas gracias por la solución,

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

gracias

# Re: Unir dos rangos en uno solo

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.

# Re: Unir dos rangos en uno solo

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

# Re: Unir dos rangos en uno solo

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.

1 best response

Accepted Solutions
best response confirmed by flopez35 (Copper Contributor)
Solution

# Re: Unir dos rangos en uno solo

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))
)``````