Coalesce cells in Excel

Problem

We'd like to coalesce cells in a row in Excel. In our example we'd like the first non-blank occurrence found for each row.

Solution

1. INDEX and MATCH in conjunction with ISBLANK

{=INDEX(A2:F2,MATCH(FALSE,ISBLANK(A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This formula works fine as long as there are not zero-length string characters ("") in the cells. E.g.: row 5 in our example. Otherwise it will return that zero-length string instead of the first number.
2. INDEX and MATCH in conjunction with EXACT

{=INDEX(A2:F2,MATCH(FALSE,EXACT("",A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This will solve the issue of cells containing zero-length string characters.

Results

Comparar dos vectores en R: elementos comunes y únicos

Problema

Deseamos comparar dos vectores en R y obtener los elemenos comunes y únicos de ambos.

Datos

# Dos vectores
[1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j"
[1] "f" "g" "h" "i" "j" "k" "l" "m" "n" "o"

a <- letters[seq(from = 1, to = 10)]
b < -letters[seq(from = 6, to = 15)]

Soluciones

Elementos comunes

# Múltiples opciones
intersect(a, b)
b[b %in% a]
a[a %in% b]

[1] "f" "g" "h" "i" "j"

Elementos únicos de a

setdiff(a, b)
a[!a %in% b]
a[b %in% a]

[1] "a" "b" "c" "d" "e"

Elementos únicos de b

setdiff(b, a)
b[!b %in% a]
b[a %in% b]

[1] "k" "l" "m" "n" "o"

Notas

Merece la pena señalar que intersect y setdiff descartarán los valores duplicados en los argumentos. Mientras que %in% conservará los duplicados. Por lo que si tuviéramos duplicados en loos vectores obtendríamos diferentes resultados. Por ejemplo, introduciendo un elemento duplicado 'a' en el vector a.

a <- c(a, "a")

[1] "a" "b" "c" "d" "e" "f" "g" "h" "i" "j" "a"

setdiff descarta los duplicados

setdiff(a, b)

[1] "a" "b" "c" "d" "e"

El operador "[]" con %in% los conserva

a[!a %in% b]

[1] "a" "b" "c" "d" "e" "a"

Entradas relacionadas

• Cómo comparar dos data frames en R
• Eliminar duplicados de un data frame en R

Referencias

• stackoverflow
• Set operations
• Value matching