☰

Excel: coincidencia de índice compleja para buscar múltiples valores

tag-icon • tag-icon microsoft-excel
Excel: coincidencia de índice compleja para buscar múltiples valores

Estoy intentando hacer una búsqueda bastante compleja en una tabla y devolver varios valores.

Necesito tomar cada uno IDcomo grupo, si Service Codeno contieneMáso si contieneExcepción, ignoralo. Si SalesyService nocoincide, ignóralo. Todo lo demás, necesito enviarlo a una lista.

Estoy muy por encima de mi cabeza y ni siquiera sé por dónde empezar.

En otras palabras, enMásservicio Salesy Serviceno puede coincidir a menos queExcepciónfue hecho. Los datos A, B, C no importan en absoluto, pero están ahí.

Lo que estoy buscando hacer

Respuesta1

Muy bien, acepté el desafío de VBA. Me confundí un poco acerca de lo que querías y dónde.Excepciónestá presente, por lo que actualmente, si hay una excepción, omitirá ese grupo por completo. Déjame saber si este no es el comportamiento que deseabas. Puede que tengas razón en que existe una fórmula y probablemente haga que esto parezca estúpidamente sobredimensionado. ¡Ojalá pueda ayudar a alguien!

Sub CalculateList()
    Dim startRowCount As Integer: startRowCount = 2     'Row to start on
    Dim columnId As Integer: columnId = 1               'Column containing ID
    Dim OutputColumn As Integer: OutputColumn = 7       'Column to output into

    '------ Begin

    Cells(1, OutputColumn).Select                       'Selects the output cell, incase there are any successes

    Dim rowCount: rowCount = startRowCount              'Sets our start row count for the loop
    Dim RowsContainingId()                              'Array containing the row ID's matching the current ID of the loop
    ReDim RowsContainingId(1)
    Dim CompletedIds()                                  'Array containing the ID's that have already been done
    ReDim CompletedIds(1)

    Do                                                  'Begin loop
nextNumberStart:                                        'Label to go back to if we hit an already completed ID
        Dim CurrentID As String: CurrentID = Cells(rowCount, columnId).Value            'Get the CurrentID of this loop
        If (CurrentID = "") Then                        'If we hit a blank, we're done, so...
            Exit Do                                     'Exit the loop
        End If
        If (IsInArray(CurrentID, CompletedIds)) Then    'Check if this ID has already been handled
            rowCount = rowCount + 1                     'It has, so select the next row
            GoTo nextNumberStart:                       '...And begin the loop again
        End If

        GetAllMatchingRowIds CurrentID, RowsContainingId, startRowCount, columnId   'Runs sub lower down to populate the array with all the rows with this ID
        If (TestIdMeetsCriteria(RowsContainingId)) Then     'Test these rows to see if it meets your criteria (function below)
            ActiveCell.Value = CurrentID                'It's a success, so write this ID in our active cell
            ActiveCell.Offset(1, 0).Activate            'And select the cell one row down, ready to write again if needed
        End If


        CompletedIds(UBound(CompletedIds) - 1) = CurrentID      'Add this ID to our completed list
        ReDim Preserve CompletedIds(UBound(CompletedIds) + 1)   'Grow our array one more ready for next completed item

        Erase RowsContainingId                          'Wipe out our Matching ID array for next loop
        ReDim RowsContainingId(1)
        rowCount = rowCount + 1
    Loop
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)           'Checks if a string is in the array
End Function

Function TestIdMeetsCriteria(arr As Variant) As Boolean
    Dim row
    Dim HasException, ValidPlus: HasException = False: ValidPlus = False        'Set our bools to False
    For Each row In arr                                             'Loop through each row containing our ID
        If (LCase(Cells(row, 2).Value) = "plus") Then               'Check for "Plus" (case insensitively)
            If (LCase(Cells(row, 3).Value) = LCase(Cells(row, 4).Value)) Then   'We got Plus, do we have matching Service and Sales (case insensitive)?
                ValidPlus = True                                    'We do. Set ValidPlus to true for comparison later
            End If
        ElseIf (LCase(Cells(row, 2).Value) = "exception") Then      'Now check if any rows contains Exception (case insensitive)
            HasException = True                                     'We do. Set HasException to true for later comparison
        End If
    Next
    TestIdMeetsCriteria = Not (HasException) And ValidPlus          'We finished the loop. If HasException is still false, and ValidPlus is now true, it was a success, otherwise a failure
End Function

Sub GetAllMatchingRowIds(idToFind As String, arr As Variant, startRowId As Integer, columnId As Integer)
    Dim rowCount: rowCount = startRowId
    Do                                                          'Loop through our ID column rows
        If (Cells(rowCount, columnId).Value = idToFind) Then    'Check if the ID is the one we're after
            arr(UBound(arr) - 1) = rowCount                     'It is, add it to our array
            ReDim Preserve arr(UBound(arr) + 1)                 'Grow our array for next lop
        End If
        rowCount = rowCount + 1                                 'Next row
    Loop Until IsEmpty(Cells(rowCount, columnId).Value)         'Keep looping until we hit a blank
    ReDim Preserve arr(UBound(arr) - 2)                         'Remove additional entries in array we don't need
End Sub

El resultado:

ingrese la descripción de la imagen aquí

Respuesta2

Si le he entendido bien, desea la lista como la anterior, excepto que desea excluir los códigos de identificación si los códigos de servicio de la identificación no incluyen un plus. Además, si las ventas y el servicio no coinciden, no se incluirán, A MENOS que haya un exceptioncódigo de servicio.

¿Has pensado en las columnas auxiliares?

La forma más sencilla de hacerlo sería concatenar las columnas A+B en la columna E, lo que le daría una fila de números de identificación y códigos de servicio.

Luego puede usar una declaración countif en la fila E para verificar dónde IDy PLUS= true.

Luego deberás verificar los nombres y las excepciones.

Luego puede usar una declaración IF anidada para verificar coincidencias, excepciones y ventajas.

En E obviamente será=CONCATENATE(a2,b2)

F se verá así=if(countif(E:E,CONCATENATE(a2,"PLUS"))>0,"PLUS","")

G que necesitarías usar para verificar los nombres, por lo que tendrías que usar=Concatenate(a2,if(c2=d2,"Match","ERROR")

H que necesitaría usar para verificar que todos los nombres estén bien o que exista una excepción.

=if(and(countif(g:G,concatenate(a2,"ERROR")>0,NOT(countif(e:e,concatenate(a2,"EXCEPTION")>0))),"No match","match")

finalmente, en I puedes buscar donde Plus = Plus y name match = Match

=IF(AND(F2="Plus",H2="Match"),"Include","Exclude")

Esto (si mis fórmulas son correctas, ¡ymmmv!) debería permitirle filtrar la lista por "INCLUIR", y listo, su lista de IDdónde servicey salees Exceptiono coincide, y al menos una de las IDentradas del código de servicio contieneplus

Respuesta3

Aquí hay una solución más flexible que VBA, aunque algo así como un flujo de trabajo híbrido manual.

Agregue una columna TRUE/ FALSEtitulada "Atendida por ventas"

=C2=D2

Luego otra columna TRUE/ FALSEtitulada "Tiene excepción"

=COUNTIFS(A:A, A2, B:B, "Exception")

Luego simplemente filtraría la tabla donde Serviced by Sales = TRUEy Has exception = FALSE. Para obtener su lista, debe copiar y pegar los ID en otro lugar y ejecutar un archivo Data > Remove Duplicates.

Si espera la generación de listas de informes en tiempo real, preferiría la solución VBA.

información relacionada