Excel 2013: VLookup для ячеек, которые имеют общие символы внутри ячейки, но оба окружены другим несоответствующим текстом

Question

Я получаю информацию из 2 разных баз данных. Базы данных используют разные протоколы именования для одного и того же элемента / указанного места размещения, однако у них всегда есть определенные общие компоненты имени. Длина этих имен может варьироваться в каждой из баз данных (см. Рис. Ниже), поэтому я не думаю, что подсчет символов поможет. Мне нужна формула (возможно, vlookup / match / index некоторого вида), чтобы связать имена из 2-го имени базы данных с 1-м именем базы данных, а затем поместить его в соседний столбец (B2) на sheet1. До этого момента мне приходилось сопоставлять, копировать и вставлять пары вручную с одного листа на другой, и это НАВСЕГДА. Любая помощь приветствуется!!!

Например:

Имя базы данных1 на листе 1, A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Имя базы данных2 на листе 2, A13: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS728X90_728X90_DFA

Общие факторы: "ROSMSNAUTOSMASSACHUSETTS" и "728X90", поэтому A2 и A13 должны быть в паре

В некоторых случаях базы данных 1 и 2 будут иметь общий аспект имени, но размер будет другим. Они должны иметь ОБА общие аспекты, чтобы быть в паре, поэтому я бы не хотел, чтобы приведенный ниже пример объединялся.

Имя базы данных1 на листе 1, A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Имя базы данных2 на листе 2, A12: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS300X250_300X250_DFA

Общий фактор: только "РОСМСНАУТОСМАССАЧУСЕТС" совпадает. "728x90" не равно "300X250" - размеры отличаются, поэтому они не должны быть в паре.

Answer 1

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

Например, если вы знаете, что соглашение об именах для DB1 всегда соответствует формату size_company_date _... , вы можете использовать что-то вроде этого, чтобы найти размер в первом идентификаторе БД:

=MID(A1,1,FIND("_",A1,1)-1)

где MID возвращает размер, посмотрев на ячейку (A1) и, начиная с первого символа, возвращает определенное количество символов (когда вы используете Find чтобы найти номер позиции первого подчеркивания строки минус 1).

Если бы вы знали, тогда, где второе значение соответствия всегда было в соглашении об именах, вы могли бы использовать формулу, чтобы найти вторую часть вашего соответствия. Эта статья дает подсказку по этому вопросу.

Используя эти знания, если вы знали, что ваше второе совпадающее значение всегда было на девять подчеркиваний слева, вы можете использовать эту формулу для возврата начальной позиции вашего второго совпадающего значения:

=FIND(CHAR(1),SUBSTITUTE(G1,"_",CHAR(1),9))+1

Итак, теперь, когда мы знаем, как найти размер и другое подходящее значение, мы можем использовать Vlookup и подстановочные знаки для сопоставления с остальными. (Прошу прощения за длинную формулу ...)

=VLOOKUP("*" & MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9)),FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),10))-FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9))) & "*" & MID(A1,1,FIND("_",A1,1)-1) & "*",B:B,1,FALSE)

Эта формула делает следующие предположения:
1. Соглашение об именовании Database1 всегда начинается с размера.
2. Соглашение об именах Database1 всегда имеет девять подчеркиваний перед вторым соответствующим значением.
3. Значения Database1 находятся в столбце A (приведенная выше формула ссылается на первое значение DB1 в A1).
4. Значения DB2 находятся в столбце B

Я использовал эту формулу и ваши примеры в Excel 2010 и она сработала для меня. Удачи!