Ich versuche, eine ziemlich komplexe Suche in einer Tabelle durchzuführen und mehrere Werte zurückzugeben.
IDIch muss jedes als Gruppe nehmen , wenn Service Codees nicht enthältPlusoder wenn es enthältAusnahme, ignorieren Sie es. Wenn SalesundService nichtÜbereinstimmung, ignorieren Sie es. Alles andere muss ich in eine Liste ausgeben.
Ich bin völlig überfordert und weiß nicht einmal, wo ich anfangen soll.
Mit anderen Worten, amPlusService Salesund Servicekann nicht angepasst werden, es sei denn, einAusnahmewurde gemacht. Die A-, B- und C-Daten sind nicht wichtig, aber sie sind da.
Antwort1
Okay, ich habe die Herausforderung für VBA angenommen. Ich war ein wenig verwirrt darüber, was du wo wolltestAusnahmeist vorhanden, daher wird diese Gruppe derzeit bei einer Ausnahme vollständig übersprungen. Lassen Sie mich wissen, ob dies nicht das gewünschte Verhalten ist. Sie haben vielleicht Recht, dass eine Formel existiert, und das Ganze sieht wahrscheinlich dumm und überentwickelt aus. Hoffentlich kann es trotzdem jemandem helfen!
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
Das Ergebnis:
Antwort2
Wenn ich Sie richtig verstanden habe, möchten Sie die Liste wie oben, außer dass Sie ID-Codes ausschließen möchten, wenn die Servicecodes der ID kein Pluszeichen enthalten. Darüber hinaus sollen Verkäufe und Service nicht eingeschlossen werden, wenn sie nicht übereinstimmen – AUSSER es gibt ein Pluszeichen exceptionim Servicecode.
Haben Sie über Hilfsspalten nachgedacht?
Am einfachsten geht das, indem Sie die Spalten A und B in Spalte E zusammenfassen. Dadurch erhalten Sie eine Reihe von ID-Nummern und Servicecodes.
Sie können dann eine Zählenwenn-Anweisung in Zeile E verwenden, um nach „where“ IDund „ PLUS=“ zu suchen true.
Sie müssen dann Namen und Ausnahmen überprüfen.
Sie können dann eine verschachtelte IF-Anweisung verwenden, um nach Übereinstimmungen, Ausnahmen und Pluspunkten zu suchen.
In E wird es offensichtlich sein=CONCATENATE(a2,b2)
F wird so aussehen=if(countif(E:E,CONCATENATE(a2,"PLUS"))>0,"PLUS","")
G müssen Sie verwenden, um die Namen zu überprüfen - Sie müssen also=Concatenate(a2,if(c2=d2,"Match","ERROR")
H müssen Sie verwenden, um zu überprüfen, ob alle Namen in Ordnung sind oder eine Ausnahme vorliegt.
=if(and(countif(g:G,concatenate(a2,"ERROR")>0,NOT(countif(e:e,concatenate(a2,"EXCEPTION")>0))),"No match","match")
schließlich kann man in I dann nach „wo Plus = Plus und name match = Match“ suchen.
=IF(AND(F2="Plus",H2="Match"),"Include","Exclude")
Dies (wenn meine Formeln richtig sind - ymmmv!) sollte es Ihnen ermöglichen, die Liste dann nach "INCLUDE" zu filtern, et Voila, Ihre Liste von IDwhere serviceund saleist entweder Exceptionoder stimmt überein, und mindestens einer der IDServicecodeeinträge von enthältplus
Antwort3
Dies ist eine flexiblere Lösung als VBA, allerdings handelt es sich in gewisser Weise um einen Hybrid aus manuellem Workflow.
Fügen Sie eine TRUESpalte FALSEmit dem Titel „Vom Vertrieb betreut“ hinzu.
=C2=D2
Dann eine weitere TRUE/ FALSESpalte mit dem Titel "Hat Ausnahme"
=COUNTIFS(A:A, A2, B:B, "Exception")
Dann filtern Sie einfach die Tabelle, wobei Serviced by Sales = TRUEund Has exception = FALSE. Um Ihre Liste zu erhalten, kopieren und fügen Sie die IDs an anderer Stelle ein und führen Sie einen aus Data > Remove Duplicates.
Wenn Sie eine Berichterstellung von Listen in Echtzeit erwarten, würde ich auf die VBA-Lösung zurückgreifen.