Agrupar columnas de datos por valores compartidos

Question

No sé cómo describir adecuadamente lo que debo hacer, así que daré un ejemplo. Un colega tiene un conjunto de datos en Excel como este:

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

El resultado final debería ser algo como esto:

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

O incluso:

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

etc.

(Si ayuda, las columnas son métodos de extracción de proteínas y las letras son ID de proteínas; debemos determinar qué proteínas se extraen mediante qué métodos)

Mi colega está haciendo esto a mano, pero hay suficientes datos que sería realmente útil automatizarlo.

¿Existe una fórmula en Excel para hacer esto automáticamente?

Answer 1

Esta no es una solución “llave en mano”, pero si tiene miles de filas, puede ahorrarle algo de esfuerzo. (Haga esto en una copia borrador de su archivo, en caso de que algo explote o se derrita, porque “Deshacer” no siempre funciona). Nota: este procedimiento fue desarrollado para Excel 2007 (pero lo he vuelto a verificar en Excel 2013).

Primero, copie todos sus datos en una columna temporal; llamémoslo V. Tenga en cuenta que debe copiar el encabezado de la columna A o poner algún valor ficticio en la celda V1.

Ahora vaya a la pestaña "Datos", grupo "Ordenar y filtrar" y haga clic en "Avanzado":

                       

Esto abrirá el cuadro de diálogo "Filtro avanzado":

                 

Verifique que "Rango de lista" muestre sus datos en la Columna V. Seleccione "Copiar a otra ubicación" y "Solo registros únicos". Escriba "W1" en el campo "Copiar a", o haga clic en el campo y luego haga clic en W1 (existen varias técnicas que obtendrán el mismo resultado). Haga clic en "Aceptar". Deberías obtener algo como esto:

                             

es decir, una lista de sus valores de datos únicos. Es posible que deba ordenar la columna W.

Ahora ingrese =NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))X2 (reemplace  4con el número de la última fila que contiene datos) y arrastre/rellene hacia abajo para que coincida con la Columna W (es decir, una fila para cada valor único en sus datos originales) y hacia la derecha con la Columna Z ( es decir, el número de columnas de sus datos).

                              

Esto le proporciona una tabla de verdad correspondiente a la segunda forma del resultado deseado en la pregunta (pero con "VERDADERO" y "FALSO" en lugar de "Sí" y "No"). Por ejemplo,

• X2 es VERDADERO porque la columna A contiene "aaaaa",
• X3 es VERDADERO porque la columna A contiene "bbbbb",
• Y2 es VERDADERO porque la columna B contiene "aaaaa",
• Y3 es FALSO porque la Columna B no contiene “bbbbb”, etc.

Elimine la columna V y corrija los títulos (en la fila 1) a su gusto. Si no desea mantener las columnas AC en la hoja de cálculo, copie las columnas WZ y pegue los valores.

---

Alguna explicación sobre la fórmula: la fórmula que he presentado anteriormente es para usar en la Columna X, que corresponde a la Columna A.  Desde que usé $W2, esta es una referencia absoluta a la columna W y se referirá a la celda cuando la fórmula se arrastre/rellene a la fila. Wnnortede cualquier columna. Por el contrario, A$2:A$4es una referencia absoluta a las filas 2 a 4, pero es una referencia relativa a la columna A. Cuando la fórmula se arrastra a la columna Y, esta referencia cambiará automáticamente a  B$2:B$4. Cuando la fórmula se arrastra a la Columna Z, esta referencia cambiará automáticamente a  C$2:C$4.