Actualmente tengo dos columnas que deben compararse. Col A, Hoja 1 y Col A, Hoja 2.
La hoja 1 contiene:
A B C
5000 Apples WI
6182 Oranges NY
7271 Grapes MN
2293 Peanuts FL
La hoja 2 contiene:
A
4032
5233
7271
2293
Debería recibir resultados como...
7271 Grapes MN
2293 Peanuts FL
Necesito mostrar solo los resultados que contienen el mismo número que la Hoja 2. ¿Existe una manera mejor que cargar la Hoja 2 en una matriz y compararla con cada celda de la Hoja 1?
'For i = 1 to Sheet1LastRow
Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)
'if cell = Sheet2Array(i, 1)
'....
'End if
Next i
'Next Cell sheet 1
Respuesta1
Esta es la función principal de =VLOOKUP().
La sintaxis es:
=VLOOKUP(
compare this cell,
to the cells in the leftmost column of this range,
returning the corresponding value from this column index,
true/false for range lookup (just leave this false if you aren't sure)
)
En la hoja 2, en la celda B1 colocarías =VLOOKUP(A1,Sheet1!A:B,2,False)y en C1 colocarías=VLOOKUP(A1,Sheet1!A:C,3,False)
Esta función funciona tanto entre hojas de trabajo como entre libros (pero deberá habilitar enlaces y estar atento a los cambios en ambos libros). No se requiere VBA, esta es una función en línea simple.
Ampliando esto, probablemente obtendrá #N/Aerrores para valores que no existen en su tabla fuente. Envuelva la fórmula =IFERROR( your vlookup() function , "" )para reemplazar cualquier error con una celda en blanco.
Respuesta2
A partir de su pregunta y los datos de muestra, es evidente que lo que está buscando como resultado es una columna con datos coincidentes en un bloque contiguo de celdas sin espacios en blanco ni errores en el medio.
Si desea utilizar un enfoque de fórmula, puede lograrlo utilizando funciones ÍNDICE y COINCIDENCIA ligeramente complejas en una fórmula de matriz. Una vez que tenga los datos coincidentes en celdas contiguas de una columna, simplemente aplique BUSCARV para recuperar el resto de las dos columnas de su tabla maestra en la Hoja1.
Así es cómo. A continuación se muestran dos capturas de pantalla de Sheet1 y Sheet2.
La Hoja1 tiene su tabla maestra en las Columnas A, B y C, la Hoja2 tiene su lista para que coincida con la Columna A de la Hoja1
Ahora en la Hoja2, digamos en la Celda C1, coloque la siguiente fórmula de matriz
{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}
Coloque esta fórmula sin las llaves y desde la barra de fórmulas presione CTRL+MAYÚS+ENTRAR para crear una fórmula matricial y arrástrela hacia abajo hasta que obtenga un error en las celdas o la longitud de la lista que debe coincidir. Ahora todas las filas encima de las celdas de error tienen las celdas coincidentes de la columna A de la Hoja1.
Simplemente borre las celdas de error y tendrá la lista en un bloque de celdas contiguo.
Ahora en D1 ingrese una fórmula BUSCARV relativamente simple para buscar la siguiente columna coincidente de la Hoja1
=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)
Y en E1 pon la formula
=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)
y arrastre ambos hacia abajo. Tienes tu lista como deseas.
Puede utilizar la opción Evaluar fórmula integrada de Excel para evaluar la combinación INDEX MATCH y tener una idea de cómo funciona. Crea una matriz de números de fila coincidentes y selecciona el primero, luego el segundo y luego el tercer número más pequeño a medida que arrastra la fórmula hacia abajo.
Dos limitaciones aquí son que los datos se devolverán en el orden en que existen en la tabla maestra y no como están en la tabla "para coincidir" y, en caso de que tenga más de una celda coincidente, se devolverá la primera.