Excel - Correspondência de índice complexo para procurar vários valores

0

Estou tentando fazer uma pesquisa bastante complexa em uma tabela e retornar vários valores.

Eu preciso levar cada ID como um grupo, se Service Code não contiver Mais ou se contiver Exceção , ignore-o. Se Sales e Service não coincidirem, ignore-o. Tudo o mais, eu preciso dar saída para uma lista.

Estou na minha cabeça e nem sei por onde começar aqui.

Em outras palavras, no serviço Mais , Sales e Service não podem corresponder, a menos que uma Exceção tenha sido feita. Os dados A, B, C não importam, mas estão lá.

    
por Tycho 16.02.2016 / 05:56

3 respostas

0

Tudo bem, aceitei o desafio do VBA. Eu fiquei um pouco confuso sobre o que você queria onde Exceção está presente, então, atualmente, se houver uma exceção, ela ignorará esse grupo completamente. Deixe-me saber se esse não é o comportamento que você queria. Você pode estar certo de que existe uma fórmula e, provavelmente, faz com que isso pareça estupidamente excessivamente engenheirado. Espero que isso possa ajudar alguém!

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

O resultado:

    
por 16.02.2016 / 08:27
0

Se eu entendi bem, você quer a lista como acima, exceto que você deseja excluir códigos de ID, se os códigos de serviço do ID não incluírem um sinal de adição. Além disso, se as vendas e o serviço não corresponderem, eles não serão incluídos - A MENOS que haja um exception no código de serviço.

Você já pensou em colunas auxiliares?

A maneira mais fácil de fazer isso seria concatenar as colunas A + B na coluna E, o que daria uma linha de números de ID e códigos de serviço.

Você pode usar uma instrução countif na linha E para verificar onde ID e PLUS = true .

Você precisaria verificar nomes e exceções.

Em seguida, você pode usar uma instrução IF aninhada para verificar Correspondências, Exceções e Vantagens.

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

F será parecido com este =if(countif(E:E,CONCATENATE(a2,"PLUS"))>0,"PLUS","")

G você precisaria usar para verificar os nomes - então você precisaria ir com =Concatenate(a2,if(c2=d2,"Match","ERROR")

H você precisaria usar para verificar se os nomes estão todos OK ou se existe uma exceção.

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

finalmente, em I, você pode procurar onde Plus = Plus e nome correspondem = Match

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

Isso (se minhas fórmulas estiverem corretas - ymmmv!) deve permitir que você filtre a lista por "INCLUDE", e voila, sua lista de ID onde service e sale é Exception ou correspondências e pelo menos uma das entradas de código de serviço ID contém plus

    
por 16.02.2016 / 11:01
0

Aqui está uma solução mais flexível que o VBA, no entanto um pouco de híbrido de fluxo de trabalho manual.

Adicione uma coluna TRUE / FALSE intitulada "Atendido por vendas"

=C2=D2

Em seguida, outra coluna TRUE / FALSE intitulada "Tem exceção"

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

Você simplesmente filtraria a tabela onde Serviced by Sales = TRUE e Has exception = FALSE . Para obter sua lista, você poderia copiar e colar os IDs em outro lugar e executar um Data > Remove Duplicates .

Se você está esperando geração de listas de relatórios em tempo real, eu gostaria de adotar a solução VBA.

    
por 16.02.2016 / 19:28