La pregunta general implica ordenar una lista grande de Excel 2007 para encontrar entradas que coincidan con una lista de subconjuntos más pequeña.
Tengo un par de ideas sobre cómo abordar el problema, pero carezco de la sofisticación técnica para implementar esas ideas. Describiré el requisito de mi caso de uso específico para aclarar la pregunta.
Ejemplo específico:
Tengo una lista maestra de nombres de empresas que administro para mi territorio de ventas (aproximadamente 1000 cuentas de clientes). Cada semana, mi empresa publica una lista de todos los negocios realizados en todos los territorios de ventas de EE. UU. (el mío y cientos de otros territorios). El registro de transacciones tiene más de 10,000 líneas, por lo que escanear a ojo para encontrar transacciones asociadas con mis cuentas es casi imposible.
Mi solución inadecuada actual es resaltar mi lista de cuentas en amarillo, copiar esa lista resaltada, luego pegarla en la parte inferior del registro de transacciones semanal, luego ordenar AZ y luego desplazarme manualmente hasta los elementos resaltados. Si el registro de transacciones contiene una de mis cuentas, la entrada del registro de transacciones estará directamente encima o debajo de la entrada resaltada que inserté. Este método es eficaz pero requiere mucho tiempo.
Sé cómo eliminar duplicados en Excel. ¿Hay alguna manera de eliminar todo MENOS los duplicados? Esto facilitaría el escaneo visual de la lista.
Otro problema persiste porque la inconsistencia de los datos ha limitado el uso de macros simples, filtros o el botón "buscar duplicados". Los nombres de los registros de transacciones a menudo se escriben ligeramente diferentes a los de mi lista maestra.
Ej: Acme Widget Company, Inc.; Acme Widget Inc; widget de Acme; Ej: Organización de Balonmano de Estados Unidos; Organización de balonmano de EE. UU.; balonmano estadounidense; USHO
Sé que hay algunas aplicaciones de terceros que pueden usar lógica difusa para hacer coincidir entradas no exactas. Sin embargo, no puedo ejecutar complementos en mi máquina empresarial. (A menos que haya un caso muy convincente...)
¿Existe alguna macro que pueda "normalizar" el registro de transacciones eliminando espacios y puntuación? ¿Existe una macro que pueda coincidir con el primer número X de caracteres (más caracteres = mayor precisión, pero mayor probabilidad de perder una entrada casi duplicada...)? ¿Existe una macro que pueda generar o filtrar la lista de "coincidencias" resultante?
Si esas tareas son demasiado complicadas, tengo una idea mucho más sencilla. Después de fusionar mi lista de cuentas resaltadas en el registro de transacciones, sería bueno poder ocultar todas las demás líneas del registro de transacciones que estén a menos de 5 líneas por encima o por debajo de mis elementos resaltados. Esto permitiría cierta flexibilidad para la ortografía no estándar, pero simplificaría enormemente la tarea de inspección visual de la lista.
Cualquier aportación sobre cómo implementar estas ideas (o enfoques completamente diferentes) será muy apreciada. Creo que la respuesta general a esta pregunta será valiosa para otros más allá del caso de uso limitado que he descrito.
¡Gracias!
Respuesta1
Definitivamente hay demasiadas preguntas para responder aquí (como comentarios de Hyperslug). Tengo una situación muy similar y descubrí que para encontrar incautos solo tenía que hacerlo manualmente ya que había demasiada variedad para codificar.
Todas las macros que sugiere se pueden escribir; si decide cuál será más efectiva, solicítela como una pregunta separada y haremos lo que podamos. El último es fácil de implementar y le ahorrará tiempo de desplazamiento. Yo crearía esa macro y luego, una vez que los duplicados estén ocultos, simplemente haga clic y arrastre la entrada "estándar" sobre las demás.
Respuesta2
Usaría la función COINCIDIR de Excel para obtener los datos que necesita, en lugar de copiarlos y ordenarlos.
Digamos que su lista maestra está en unrango con nombrese llama Master y el nombre de la empresa en el registro de transacciones está en la columna D. En algún lugar de la fila de la transacción, ingrese la siguiente fórmula: =IF(ISNA(MATCH(D1,Master,0)),0,1)y cópiela en todas las filas de la tabla de transacciones. Esta fórmula dará como resultado 1 si el nombre de la empresa coincide y 0 en caso contrario.
Esto sólo coincidirá con nombres exactos. Lo que tendrás que hacer es agregar nombres alternativos al rango Master (asegúrate de ordenarlo después de agregar nombres) para obtener todas las versiones posibles.
Respuesta3
Estoy de acuerdo con el enfoque de agregar ortografías alternativas a su lista maestra (es posible que tenga una segunda columna para indicarle cuál es su formato preferido para el envío de correo, etc., y cuál es solo para coincidir con los datos de la empresa). Es posible que tenga cierto éxito utilizando funciones SUSTITUIR sucesivas para generar una versión alternativa de los nombres. p.ej
=SUSTITUIR(SUSTITUIR(SUSTITUIR(BAJO(A1)," inc",""),".","")," ","")...
Entonces, cada sustitución reemplaza cualquier instancia del texto seleccionado con el reemplazo; nada en nuestro caso aquí. Según mi experiencia con coincidencias difusas similares entre nombres de sistemas dispares, es posible que tengas que eliminar cosas como inc, corp, plc, etc. para obtener coincidencias. Si bien puedes usar SUSTITUIR para esto, podrías obtener algunos resultados extraños si cosas como "Income Corporation" se convierten en "omeorporación", por lo que puede ser más seguro usar este tipo de cosas:
IF(DERECHA(inferior(A1),4)="corp",izquierda(inferior(A1),len(A1)-4)),inferior(A1)).
Haga el sustituto de los espacios al final.
Puede usar COINCIDIR o CONTAR.SI con resultados similares para obtener una columna que muestre qué transacciones coinciden con su lista.
Una alternativa sería utilizar su lista maestra como criterio para basar un filtro avanzado, lo que le permitiría tomar muy fácilmente una copia de las entradas de la lista de transacciones que coinciden con los nombres de sus clientes y colocar esta copia filtrada en otro lugar (por ejemplo, en una cara o en otra hoja). Al igual que con lo anterior, aún necesitarás agregar variantes cuando estén demasiado alejadas de tu nombre original.
Respuesta4
Me preguntaba si había intentado utilizar una tabla dinámica. Recojo una gran cantidad de datos usando PT y me ayudan a analizar los problemas de múltiples maneras muy rápidamente y con total integridad de los datos.
Resalte todos sus datos y seleccione insertar tabla dinámica. Ahora podrá revisar sus datos de muchas maneras interactivas que le permitirán limitar las molestas entradas dobles, errores ortográficos, etc. Luego podrá ordenar usando ordenaciones personalizadas, etc., así como AZ.