Подсчитать повторяющиеся ячейки (значения, текст и пусто) при сравнении двух столбцов

Question

У меня есть две таблицы со следующими структурами данных:

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

То, что мне нужно найти, это повторяющиеся значения из листа б против листа а.

Я пробовал функции countif , match , vlookup и index , но все же я не доволен результатом. Нужна позиция, потому что дубликат повторяется более 3 раз, найдите точный идентификатор позиции для этого дубликата. Как найти повторяющиеся значения на двух разных листах?

Answer 1

Похоже, вы в порядке с несколькими дубликатами, но для любого старше 3, вы хотите увидеть их.

Я собрал UDF под названием "xMatch", который может вам помочь. Это так же, как Match, в котором он возвращает позицию значения, но позволяет указать, что вы хотите найти n-е значение (например, 3-й дубликат).

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

Чтобы он работал, вам нужно вставить этот код в модуль (я объясню, как ниже, если вы незнакомы):

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

Чтобы разместить этот код, нажмите Alt + F11 из файла Excel, и он откроет редактор VBA. На панели инструментов выберите « Вставить» и выберите « Модуль».

Откройте новый модуль и вставьте код в!

Теперь, когда вы введете «= xMatch(» в ячейку), вы сможете использовать новую формулу.

Надеюсь это поможет!