Сравнение двух столбцов с частичными значениями и получение информации о совпадении и номере столбца

Question

Я использовал VLOOKUP, MATCH, INDEX и даже дополнение Fuzzy. Я уверен, что один из них или их комбинация может сработать, но у меня просто нет ноу-хау, чтобы заставить его работать и постоянно получать "# N/A" ...

Проблема: у меня есть 2 столбца с различным общим количеством элементов, полных ссылок. У одного частично совпадают номера другого, с некоторыми изменениями, например:

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

Но у меня есть тысячи чисел, и я хочу сопоставить каждую ячейку от A до всего диапазона B, и, если есть совпадение, даже в ячейке B6544, я хочу узнать и получить что-то (в столбце C), например, "True B6544".

(Небольшой) пример того, с чем я имею дело (а не точные ссылки, это внутренние разумные данные):

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

Как видите, J1 является частичным совпадением с B1, поэтому H1 возвращает «True, B1».

Как было сказано выше, я попробовал MATCH, VLOOKUP, INDEX и дополнение Fuzzy. Я так понимаю, что мне так нужно

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

Но, кажется, ничего не работает ... любая помощь будет оценена!

Answer 1

Попробуйте эту небольшую пользовательскую функцию:

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

Как показано на рисунке, в K1 введите:

=partialmatch($B$1:$B$10,J1)

и скопировать вниз.

Процедура удаляет начальную цифру из подстроки и пытается найти ее в столбце.

Answer 2

Поскольку Excel не поддерживает регулярные выражения, я не думаю, что есть формула, которая не нуждается во вспомогательном столбце. В моем примере

• А содержит числа с "-"
• B содержит числа, которые соответствуют
• C содержит значения B без первой цифры
• D равно "FALSE", если значение A не соответствует ни одному в столбце B, или "TRUE" + ссылка на соответствующую ячейку.

Формула в С1:

=RIGHT(B1,LEN(B1)-1)

Это удаляет первую цифру B1.

Формула в D1:

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

Обратите внимание, что, хотя это должно привести к желаемому результату, я бы рекомендовал разделить TRUE/FALSE и ссылку на ячейку на два столбца. Поэтому я объясню только часть ADDRESS , которая даст вам ссылку на ячейку, если совпадение найдено, или ошибку в противном случае.

VLOOKUP здесь бесполезен, потому что он будет возвращать значение в той же строке, что и соответствующее значение. MATCH , с другой стороны, возвращает строку соответствующего значения.

• MID(A1,2,SEARCH("-",A1)-2) возвращает подстроку A1, начиная со второго символа, вплоть до первого исключения "-" и исключая его. Это значение, которое мы ищем (lookup_value).
• MATCH(lookup_value, C$1:C$10, 0) вернет строку первого вхождения lookup_value . Поскольку мы не можем применить функцию к диапазону, а Excel не поддерживает регулярные выражения, нам нужен вспомогательный столбец C. Последний параметр (0) необходим, поскольку значения не отсортированы. Обратите внимание, что возвращаемое значение строки относится к указанному диапазону, поэтому, если ваш диапазон не начинается с строки 1, вам придется учитывать это (например, добавив ROW([first cell])-1 к результату MATCH).
• ADDRESS(matched_row, COLUMN(B1)) создает ссылку на ячейку. Вы можете использовать абсолютное количество строк вместо COLUMN(B1) если хотите, но это было бы не так легко читаемо.

Вы должны решить для себя, какие ссылки должны быть абсолютными или относительными.

Answer 3

Я пытаюсь частично ответить на ваш вопрос, поскольку у нас пока нет полной картины.

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

Столбец C-helper удаляет 0 в начале, а конечный - number .
Столбец I- helper удаляет первую цифру (или первую цифру) столбца J col
Затем я сделал vlookup данных в I-helper против C - helper и выяснил, найдены ли данные или нет, основываясь на результате vlookup ().

Формула, которую я имею в

• C - helper : ==MID(B4,2,LEN(B4)-3)
• I - helper : =MID(D4,2,LEN(D4)-1)
• vlookup col : =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

Следующая часть проблемы - найти, где была найдена эта запись. Если запись найдена - ее адрес возвращается, иначе возвращается строка Not_Available is returned . Пример данных теперь выглядит так

A   B           C - helper  J           I - helper  vlookup col H       I   X-helper
1   025983553-1 25983553    225983553   25983553    found       True,   B1  $C$4
2   025973223-1 25973223    222222345   22222345    not_found   FALSE       Not_Available
3   025965463-2 25965463    233444667   33444667    not_found   FALSE       Not_Available
4   025911122-4 25911122    211198989   11198989    not_found   FALSE       Not_Available
5   025998764-1 25998764    212989238   12989238    not_found   FALSE       Not_Available
6   025925925-3 25925925    224397501   24397501    not_found   FALSE       Not_Available
7   025900000-2 25900000    225973223   25973223    found   True,       B2  $C$5
8   025999999-5 25999999    223334445   23334445    not_found   FALSE       Not_Available
9   025965453-6 25965453    211100110   11100110    not_found   FALSE       Not_Available
10  025943536-2 25943536    225911122   25911122    found   True,       B4  $C$7

редактировать

Формула, которую я имею в

• X - helper : =IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")