Combinar filas de Excel desde dos hojas

Me gustaría comenzar esta pregunta pidiendo disculpas, hay numerosas preguntas sobre la fusión de las filas de excel, aún, después de navegar por la web Y la lista de " Preguntas que ya pueden tener su respuesta ", todavía no sé cómo resolver mi problema.

EDIT : Parece que las posibles soluciones pueden diferir según el tipo de datos dentro de las diferentes hojas de cálculo que me gustaría fusionar. En este caso, parece que el uso de la función "Consolidar" resuelve el problema una vez que todos los registros de datos (pero la columna más izquierda) son numéricos. Mi problema es encontrar la manera de encontrar una solución para datos mixtos de números y texto (el texto puede contener también letras en inglés). La pregunta fue editada para proporcionar mejores ejemplos.

Tengo dos hojas de cálculo con datos diferentes. La única similitud es en los valores de la columna muy izquierda:

Ejemplo de hoja de cálculo 1, en realidad contiene miles de registros y no tiene nada que ver con las cadenas de suministroEjemplo de hoja de cálculo 1, en realidad contiene miles de registros y no tiene nada que ver con las listas de precios

Ya he puesto los datos de ambas hojas de cálculo en uno, pegando los valores de la columna más izquierda de una de las hojas de cálculo debajo de los valores de la columna más izquierda de la segunda hoja de cálculo. Para el resto de los valores, los he pegado a la derecha, por lo que las columnas de diferentes hojas de cálculo no se mezclan. Después de hacer eso, he clasificado la hoja de cálculo por los valores de la columna más izquierda y obtuvo un resultado similar al ejemplo simplificado en la ilustración:

Datos de las hojas de cálculo 1 y 2, combinados en una hoja de cálculo, clasificados pero no combinados

Lo que quiero hacer con esta hoja de cálculo, es eliminar las filas duplicadas y fusionar los datos de filas duplicadas, en función de la columna más a la izquierda. Un ejemplo correspondiente se proporciona en la ilustración:

Introduzca aquí la descripción de la imagen

Algunas notas importantes :

Prefiero trabajar con las hojas de cálculo de origen, en lugar de combinarlas con una hoja de cálculo intermedia intermedia, pero acepto cualquier solución que me ayude a lograr mi objetivo.

Los registros de datos (como se muestra en las figuras anteriores) pueden ser incompletos y contener tanto números como texto. Además, los registros de datos pueden estar equivocados, por lo que no puedo asumir que una columna determinada siempre tendrá números o texto (por ejemplo, la columna Envío de la hoja de cálculo 2).

Hay filas en las que la mayoría del valor de la columna izquierda está presente en sólo una de las hojas de cálculo (por ejemplo, "nombre5" sólo proviene de la hoja de cálculo 1, mientras que "nombre3" y "nombre6" sólo provienen de la hoja de cálculo 2).

Estoy tratando de evitar el uso de macros VB para esto, y prefieren utilizar funciones incorporadas ya presentes en Excel.

Por otro lado, la exportación a CSV y el uso de la expresión regular es algo que estoy dispuesto a considerar, si no hay manera de lograr esta tarea utilizando Excel herramientas integradas. En este caso, la pregunta puede necesitar encontrar una nueva casa en StackOverflow.

Para datos numéricos …

Pruebe a utilizar la función consolidar incorporada.

Digamos que tengo el ff. Datos en Sheet1 y Sheet2 , respectivamente.

Introduzca aquí la descripción de la imagen

  1. Seleccione una celda de destino. Cualquier celda / rango lo hará siempre y cuando tenga suficiente espacio para el resultado. Recomiendo crear una nueva hoja y resaltar la celda A1 .
  2. Vaya a Datos > Consolidar . También puede pulsar Alt + A , N.
  3. Elija Sum .
  4. En Referencia , haga clic en el botón de selección de referencia y resalte los datos en Hoja1 (incluidas las etiquetas de fila y los encabezados). Haga clic en Agregar .

    Introduzca aquí la descripción de la imagen

  5. Repita el paso anterior pero agregue los datos en Sheet2 en su lugar.

    Introduzca aquí la descripción de la imagen

  6. En Usar etiquetas , asegúrese de que la fila Superior y la columna izquierda estén marcadas. Tendrás algo como esto:

    Introduzca aquí la descripción de la imagen

  7. Haga clic en Aceptar .

Resultado:

Introduzca aquí la descripción de la imagen


Para varios tipos de datos …

  1. Utilice la función Consolidar para obtener las etiquetas de fila y los encabezados de columna únicos. Borre los datos pero deje las etiquetas / encabezados:

    Introduzca aquí la descripción de la imagen

  2. Introduzca esta fórmula aproximada en la celda en blanco más a la izquierda de la tabla y cópiela a través y hacia abajo. Reemplace los rangos con nombre para que se ajusten a sus datos.

    =IFERROR(IFERROR( INDEX(sheet1_data,MATCH($A2,sheet1_rowlabels,0),MATCH(B$1,sheet1_headers,0)), INDEX(sheet2_data,MATCH($A2,sheet2_rowlabels,0),MATCH(B$1,sheet2_headers,0))), "")

    Dónde
    sheet1_data , sheet1_rowlabels y sheet1_headers refieren a todos los datos (A1: C5), la primera columna (A1: A5) y la primera fila (A1: C1) en Sheet1, respectivamente
    sheet2_data , sheet2_rowlabels y sheet2_headers refieren a todos los datos (A1: D6), la primera columna (A1: A6) y la primera fila (A1: D1) en Sheet2, respectivamente

  3. Formato como desee.

    Introduzca aquí la descripción de la imagen

Si entiendo su pregunta correctamente, esto se puede manejar de una manera directa usando VLOOKUPs.

En primer lugar, tendrá que crear una lista sin ordenar y ordenada de los valores de "Elemento" que se encuentran en las columnas de la izquierda de ambas tablas (a lo largo de las líneas que se muestran en el ejemplo "Resultado final").

Esto se puede hacer por:

  • Copiar la lista de elementos de la primera tabla en la columna A de la hoja que desea conservar el resultado final
  • Copiar la lista de elementos de la segunda tabla y pegarla debajo de los valores que copió de la primera tabla para que tenga una lista duplicada larga
  • Clasificación de la lista duplicada mediante la función de clasificación integrada de Excel en la cinta de inicio
  • Eliminando los duplicados resaltando la lista con el ratón y seleccionando Data / Remove Duplicates de la cinta

A continuación, puede proceder a configurar la tabla de resultados finales con la lista de valores de elementos únicos como las etiquetas de fila y las etiquetas de columna copiadas de las dos tablas (de nuevo como se muestra en el ejemplo de Resultado final).

Con este shell en su lugar, puede introducir las funciones VLOOKUP que extraerán los datos de las dos tablas. La idea es usar un conjunto de VLOOKUPs para obtener los datos de la primera tabla y otro conjunto para obtener los datos de la segunda tabla.

Esta es la fórmula de la primera celda de la tabla Resultado Final:

  =IFERROR(IF(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)=0,"-",VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)),"-") 

La sintaxis para la búsqueda es VLOOKUP( lookup_value, table_array, column_index_num, [range_lookup]) . El último parámetro opcional "range_lookup" especifica si la coincidencia de la búsqueda será aproximada (la predeterminada) o exacta.

Por lo tanto, los VLOOKUPs de la fórmula (ambos VLOOKUPs son idénticos) buscan el nombre del elemento en la celda A2 en la primera columna del rango A2: C5 y devuelven el valor correspondiente en la segunda columna del rango, que es la columna Supplier. Como el último argumento en VLOOKUP se establece en 0 (o FALSE), la coincidencia debe ser exacta.

Proveedor de VLookup en la Tabla 1

IFERROR, IF y doble uso de VLOOKUP en la fórmula son necesarios para tratar la posibilidad de que no haya coincidencia en el ítem (lo que podría ocurrir si el elemento "nombre1" está en la tabla 2 pero no en la tabla 1) o que allí Es una coincidencia en el elemento, pero la celda del valor correspondiente está vacía (que de lo contrario devolvería 0).

La fórmula de búsqueda para la segunda columna de la tabla de resultados finales difiere de la primera sólo en el número de índice de columna, que se establece en 3 para devolver la información de contacto:

Vlookup Contacto en la Tabla 1

Exactamente el mismo enfoque se utiliza para obtener los datos de la segunda tabla de entrada, utilizando un VLOOKUP que hace referencia al rango para esa tabla y las respectivas columnas de datos que se van a devolver. Tenga en cuenta que he configurado las fórmulas para devolver un guión ("-") si no se encuentra ninguna coincidencia o si no hay valor de retorno disponible (es decir, si la celda con el valor de retorno está vacía). Esto se puede cambiar fácilmente a una cadena vacía (""). Por supuesto, una vez que las fórmulas para la primera fila de la tabla de resultados se configuran con referencias adecuadas a los rangos de tablas y columnas de retorno, se copiarán al final de la lista de elementos.

Precio de Vlookup en la tabla 2