Policz duplikaty komórek (wartości, tekst i puste) podczas porównywania dwóch kolumn

Mam dwa arkusze z następującymi strukturami danych:

sheet a) Id   Name  abn  address    sheet b) Id  Name abn  address
         1    AA    123  ac                  25  ad   124  ab
         015  Ac    125  aw                  02  aa   123  ac
         26   3m         az                  8   aap  234  df
         32   im    98   op                  17  aj        aw
         230  aap   234  df                  15  3m   160  az

Muszę znaleźć zduplikowane wartości z arkusza b na arkuszu a.

próbowałem countif, match, vlookup, i index Funkcje, ale nadal nie jestem zadowolony z wyniku. Potrzebujesz pozycji, ponieważ duplikat jest powtarzany ponad 3 razy, znajdź dokładny identyfikator pozycji dla tego duplikatu. Jak znaleźć zduplikowane wartości z dwóch różnych arkuszy?

microsoft-excel worksheet-function

— Nikola Karcev
źródło

Czekaj, jestem zdezorientowany, próbujesz znaleźć zduplikowane wartości lub lokalizacje lub tylko liczbę duplikatów?

— James Mertz

Twoje pytanie jest bardzo mylące. Pokazujesz cztery kolumny, ale bez wiersza sheet a dopasowuje dowolny wiersz sheet b we wszystkich czterech kolumnach –– więc musisz pokazać je wszystkie? Czy szukasz wszystko zduplikowane wartości, takie jak a.Name[3] = 3m = b.Name[5], a.Name[5] = aap = b.Name[3], a.Name[1] = AA = b.Name[2] = aa (bez rozróżniania wielkości liter), a.Id[2] = 015 = b.Id[5] = 15 (ignoruj wiodące zera), a.abn[1] = 123 = b.abn[2], a.abn[3] = (puste) = b.abn[4]i wszystkie mecze w address kolumna? Czy interesuje Cię tylko jedna kolumna? … (nieprzerwany)

— Scott

(ciąg dalszy)… Jeśli interesuje Cię tylko jedna kolumna, (a) powiedz tak, a (b) pokaż, że ta kolumna jest pierwsza, może z inną, aby zilustrować powiązane dane - nie zaśmiecaj pytania nieistotnością. Jeśli interesuje Cię więcej niż jedna kolumna, przeformułuj swoje pytanie (tytuł) - może „porównaj dwa arkusze”? Pokaż nam, jak ma wyglądać wynik. Czy chcesz policzyć, znaleźć pozycję lub jedno i drugie? Co ma wspólnego „3 razy” z pytaniem? Podaj nam przynajmniej jeden przykład dokładnie co próbowałeś i dlaczego uznasz to za niezadowalające.

— Scott

Wygląda na to, że wszystko jest w porządku z kilkoma duplikatami, ale dla każdego powyżej 3, chcesz je zobaczyć.

Przygotowałem UDF o nazwie „xMatch”, który może ci pomóc. Podobnie jak Match, ponieważ zwraca pozycję wartości, ale pozwala określić, czy chcesz znaleźć n-ty wartość (np. trzeci duplikat).

=xMatch("Look for", "Look in this column", "Find the nth one")

Aby to działało, musisz wstawić ten kod do modułu (wyjaśniam, jak poniżej, jeśli nie znasz):

Public Function xMatch(lookup_value As String, column_array As Range, find_nth As Integer)'

Dim aSize As Integer 'Rows in the column_array
Dim Hit() As Long 'Array that keeps track of all match locations
Dim i As Long 'Iterator
Dim z As Long 'Counts zeroes
Dim Pos As Integer 'Position of our desired match in the column array

aSize = column_array.Rows.Count
ReDim Hit(1 To aSize)

'Check each cell in the range for matches
'When a match is found, note it's postion in the Hit array
'If a match isn't found, add another zero to the count
For i = 1 To aSize
 If (InStr(1, column_array(i), lookup_value) > 0) Then
  Hit(i) = 1 * i
 Else
  z = z + 1
 End If
Next i

'Small finds the kth smallest number, but considers ties as seperate numbers
'Consider {1,0,0,2}
'1st smallest number is 0, and the second smallest number is also 0
'So we need to screen out the all the zeros (z) and then find the nth item after that
Pos = WorksheetFunction.Small(Hit, z + find_nth)

xMatch = Pos

End Function

Aby umieścić ten kod, naciśnij Alt + F11 z pliku Excela, a otworzy się edytor VBA. Na pasku narzędzi wybierz Wstawić i wybierz Moduł .

Otwórz nowy moduł i wklej kod w!

Teraz, gdy wpiszesz „= xMatch (” w komórce, pozwoli Ci użyć nowej formuły.

Mam nadzieję że to pomoże!

— Aaron Contreras
źródło