Tengo una lista de ID y descripciones en la Hoja 1. Estos datos luego se usan en una segunda hoja (Hoja 2) con la que el usuario puede interactuar.
La lista en la Hoja 1 tiene un ID que se repite, pero tiene una descripción diferente. ¿Cómo podría usar la validación de datos en la Hoja 2 para combinar todas las repeticiones del número de identificación 4 en 1, pero permitir al usuario seleccionar la descripción (usando un cuadro desplegable) que desee, después de realizar la selección en la Columna A? ?
Me gustaría mantenerme alejado de las macros para esto, utilizando únicamente funciones integradas.
¡Cualquier ayuda es apreciada!
Respuesta1
Combina las repeticiones en la Hoja1. Las fórmulas de búsqueda devuelven solo el primer valor encontrado, por lo que si tiene varios del mismo ID, deberá crear una búsqueda con ID únicos. Prepárelo en una tabla auxiliar y use la tabla auxiliar para la validación de datos si no desea o no puede cambiar la tabla original.
Es bastante engañoso que un identificador (que por algo se llama así) no sea único.
Respuesta2
No has mencionado la versión de Excel que estás utilizando. Sugiero una posible solución basada en mi comprensión de su pregunta. Sin embargo, también utiliza funciones denominadas IFERROR & COUNTBLANK que supongo que no están disponibles antes de Excel 2007. Por lo tanto, en caso de que tenga Excel 2003, es posible que necesite un enfoque diferente.
Puede que esta no sea la solución ideal, ya que utiliza columnas auxiliares y también una hoja auxiliar.
En este ejemplo, los datos de muestra se encuentran en la Hoja1!A2:B10, como se ve en esta captura de pantalla.
Primero necesitamos crear una columna auxiliar en la Columna D para obtener la lista única de sus ID.
En D2, coloque la siguiente fórmula y luego presione CTRL+ SHIFT+ ENTERdesde dentro de la barra de fórmulas para crear una fórmula matricial. También debes dejar una fila (preferiblemente la primera fila) como encabezado para que esto funcione correctamente.
La fórmula deberá estar entre llaves para indicar que es una fórmula matricial.
Arrástrelo hacia abajo hasta las filas deseadas según el recuento esperado de valores únicos en su columna maestra. Con el tiempo, comenzará a devolver espacios en blanco en la parte inferior y luego podrá detenerse. Esto crea una lista maestra única de ID.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
Ahora necesitamos saber el inicio y el final exactos de esta lista para completar la Lista de validación.
Ahora en G3 pon la siguiente fórmula.
=INDEX(Sheet1!D2:D10,1)
y en G4 ponga la siguiente Fórmula de Matriz.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
No olvides CTRL+ SHIFT+ ENTERpara este.
Ahora vaya al Administrador de nombres y cree un nuevo nombre llamado MiLista
Pon la siguiente fórmula allí.
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
Aquí usamos ÍNDICE para devolver una referencia de celda en lugar de un valor
Inserte una nueva hoja llamada Hoja2.
Ahora, en la Col A2 y debajo, cree una Lista de validación y colóquela =MyListallí.
Ahora todos los valores únicos aparecen en la primera lista desplegable de ID.
Esto completa la Parte 1.
Ahora la siguiente tarea es completar una segunda lista de validación basada en los datos extraídos de la columna Descripción.
Inserte una tercera hoja auxiliar llamada Hoja3. Puedes simplemente ocultar esta hoja.
En este ejemplo he tomado datos en la Hoja3!B2:H10. Decida la cantidad de columnas según la cantidad máxima de duplicados en sus ID de la lista maestra. También deje la primera columna A para que esto funcione correctamente.
En B2, coloque la siguiente fórmula y presione CTRL+ SHIFT+ ENTERdesde la barra de fórmulas para crear una fórmula de matriz.
Arrástrelo hacia abajo y hacia abajo según lo previsto.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
De vuelta en el Administrador de nombres, cree un nuevo nombre llamado Recortado y coloque la siguiente fórmula en él.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
Esto genera una lista que excluye los espacios en blanco para que la lista se recorte correctamente.
Ahora, en la Hoja 2 para las celdas B2 y hacia abajo, cree una Lista de validación y colóquela =Trimmedallí.
Ahora, a medida que sus datos cambian en Hoja1!A2:A10, la lista de valores únicos se refleja automáticamente en MiLista y, además, el nombre Recortado obtendrá solo la Descripción deseada de la columna Descripción de Hoja1.
Consulte la siguiente pantalla de pantalla a GIF para tener una idea de cómo funciona todo.
