Множественный ВПР в одной ячейке по части текста?

Question

[Ieskelahsydyv]

Каждый день сталкиваюсь с рутинной задачей работы подбора замен номеров.

Есть исходная таблица со всеми возможными артикулами без дубликатов.

Есть вторая таблица в которой есть замены.
Как сделать общую таблицу с 2 колонками АРТИКУЛ и ЗАМЕНА, во второй колоне вывести, без дубликатов, все возможные варианты замен.

АРТИКУЛ          ЗАМЕНА                                       АРТИКУЛ ЗАМЕНА
A001                  A001/A002                                     A001 A002/A003/A009/A010
A002                  A002/A003                                     A002 A001/A003/A009/A010
A003                  A003                                              A003 A001/A002/A009/A010
A004                  A004/A005/A006/A007                  A004 A005/A006/A007/A008
A005                  A00555                                          A005 A004/A006/A007/A008
A006                  A006                                              A006 A004/A005/A007/A008
A007                 A007/A008                                    A007 A004/A005/A006/A008
A008                 A008                                              A008 A004/A005/A006/A007
A009                 A009/A010/A001                           A009 A001/A002/A003/A010
A010                 A010                                              A010 A001/A002/A003/A009

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

Comments

[Александр]

Ieskelahsydyv, из описания не понятно, какой результат вам нужен. Сделайте пример в Гугл-Таблицах и расшарьте её или приложите к вопросу читаемые картинки с конечным видом таблицы.

[Ieskelahsydyv]

Александр,

[Ieskelahsydyv]

John Smith, попробовал визуально отобразить

[Александр]

Ieskelahsydyv, откуда у А006 взялась альтернатива А008, если судить по третьей таблице?

[Ieskelahsydyv]

Александр, A004 имеет замену на A006 и также A004 имеет замену A005/A007 а A007 имеет замену на A008 следовательно A006 имеет замену A008

[Ieskelahsydyv]

Александр, вот из-за такого сложного поиска вручную всех замен ежедневно, просто нереально сделать чтобы не ошибиться

[Ieskelahsydyv]

John Smith, отсутствует возможность установить сторонние программы, стоит только excel.

[Александр]

Ieskelahsydyv, получается, что процентов девяносто у вас взаимозаменяемы?

[Ieskelahsydyv]

Александр, нет, просто я привел пример в котором так получилось.
Алгоритм такой:
1) беру 1е значение из 1й таблицы и ищу где данное значение встречается во второй таблице и вывожу все совпадения через "/" удаляя дубликаты
2) каждое значение из совпадений также ищу где данное значение встречается во второй таблице и так пока все связи не будут найдены.

[Александр]

Ieskelahsydyv, ОК,а дальше что? Конечная задача?

[Ieskelahsydyv]

Александр, 2 столбца АРТИКУЛ и ЗАМЕНА в котором через "/" все замены

[Ieskelahsydyv]

John Smith, используется для формирования заказа товаров, функция впр вытягивает замены соответствующие артикулам указанных в заказе и вставляет их в колонку замены и сейчас вручную просматриваются замены на наличие на складе, но планируется функцией перебирать замены указанные через "/" и выводить актуальную замену с необходимым колвом на складе.

[Ieskelahsydyv]

John Smith, нет возможности установить стороннее по и нет знаний в организации бд, может подскажите как приблизиться к решению

[Александр]

1. Обязательно выставлять совместимость заменяемость младших артикулов старшими. От старших к младшим можно через формулы извернуться.
   
2. «Въехать» в VBA.
   
3. «Въехать» в базы данных.
   
4. Access на ПК есть? Начать использовать.
   
5. Онлайн-решения допустимы? Начать использовать.
   

[Александр]

Обязательно выставлять совместимость заменяемость младших артикулов старшими.

Не отметил, что совместимость заменяемость должна выставляться в машиноудобном виде, как у John Smith в комментарии выше. В читаемый человеком вид привести всегда можно.

[Ieskelahsydyv]

Александр,

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

Что значит выставить совместимость. Нет ни младших ни старших.

«Въехать» в VBA.

Пытаюсь, поэтому я тут

«Въехать» в базы данных.

въезжаю но решения не нашел еще

Access на ПК есть? Начать использовать.

да есть, пробывал, пока решения не нашел

Онлайн-решения допустимы? Начать использовать.

Какие именно?

[Александр]

Что значит выставить совместимость. Нет ни младших ни старших.

Я про заменяемость.

Чем ниже в столбце артикул, тем он «старше», потому что номер строки у него больше. Конечно, если отсортируете как либо, всё или что-то изменится.

Вы же используете эл. таблицу как базу данных, поэтому думать нужно соответственно.

Например, артикул а001 может заменяться артикулом а50000. Добавляем строку:

Артикул  Замена
а001     а50000

Обратная пара не нужна! Её вычислить можно будет.

(Симбиоз мышления эл. таблицами и базами данных — тот ещё спорт, но работает.)

[Ieskelahsydyv]

Александр, применив макрос более менее приблизился к результату.

Answer 1

[Григорий Боев]

В Excel на VBA что-то возможно подобное сделать.
В Google таблицах есть шанс обойтись формулами, хотя и пользовательскую функцию там проще сделать.

на формулах это жутко выглядит, да и ошибиться можно на раз. Формула для 3х итераций

=join(
        "/"
        ;ТРАНСП(unique(
                ТРАНСП(split(
                        join(
                                "/"
                                ;ArrayFormula(
                                        ЕСЛИОШИБКА(
                                                ВПР(
                                                        split(
                                                                join(
                                                                        "/"
                                                                        ;ArrayFormula(
                                                                                ЕСЛИОШИБКА(
                                                                                        ВПР(
                                                                                                split(
                                                                                                        ВПР(A1;FILTER(A:B;A:A<>"");2;0)
                                                                                                        ;"/"
                                                                                                )
                                                                                                ;FILTER(A:B;A:A<>"")
                                                                                                ;2
                                                                                                ;0
                                                                                        )
                                                                                        ;split(
                                                                                                ВПР(A1;FILTER(A:B;A:A<>"");2;0)
                                                                                                ;"/"
                                                                                        )
                                                                                )
                                                                        )
                                                                )
                                                                ;"/"
                                                        )
                                                        ;FILTER(A:B;A:A<>"")
                                                        ;2
                                                        ;0
                                                )
                                                ;split(
                                                                join(
                                                                        "/"
                                                                        ;ArrayFormula(
                                                                                ЕСЛИОШИБКА(
                                                                                        ВПР(
                                                                                                split(
                                                                                                        ВПР(A1;FILTER(A:B;A:A<>"");2;0)
                                                                                                        ;"/"
                                                                                                )
                                                                                                ;FILTER(A:B;A:A<>"")
                                                                                                ;2
                                                                                                ;0
                                                                                        )
                                                                                        ;split(
                                                                                                ВПР(A1;FILTER(A:B;A:A<>"");2;0)
                                                                                                ;"/"
                                                                                        )
                                                                                )
                                                                        )
                                                                )
                                                                ;"/"
                                                        )
                                        )
                                )
                        )
                        ;"/"
                ))
        ))
)

Демонстрационная таблица

Comments

[Ieskelahsydyv]

Григорий, благодарю за отклик. Нет возможности использовать интернет гугл таблицы и стороннее по и флеш на рабочем пк, доступ только к ексель файлу с порядка 100 000 артикулов которые периодически добавляются и добавляются варианты замен.
Пытался через формулу в Excel {=ОБЪЕДИНИТЬ("/";;ЕСЛИ(ТАБЛИЦА1[@АРТИКУЛ]=ТАБЛИЦА2[ЗАМЕНА];ТАБЛИЦА2[ЗАМЕНА];""))}
она работает, но ограниченно, находит только четкий артикул без "/", но если использовать символы
"*"&"/"&[@АРТИКУЛ]
"*"&"/"&[@АРТИКУЛ]&"/"&"*"
[@АРТИКУЛ]&"/"&"*"
то результата нет, да и перекрестный поиск данная формула не делает.
Исходя из вашего решения

Результат должен быть таким

т.к. A0002 встречается во 2,3 5-8 строках и имеет множество замен

[Григорий Боев]

Ieskelahsydyv, сделайте так:
1) замените в списке замен артикулы на их замены. Чтобы искать артикул а не его часть - дополните слэшем слева или справа, "/A001/A002/A003"
2) Повторите п.1 только вместо замен используйте результат п.1
Получится несколько столбцов, последний из них - то что вам нужно.
3) Удалить дубликаты.
В Google таблицах это в пару формул делается, как там сейчас дела в Excel обстоят - не знаю, уже давно пересел на GoogleSheets.
Можно искать текст до слэша, и заменять в остатке строки этот артикул на пустую строку. И так по каждому артикулу.

Получится громоздко, но работать будет

[Ieskelahsydyv]

Григорий Боев,

1) замените в списке замен артикулы на их замены. Чтобы искать артикул а не его часть - дополните слэшем слева или справа, "/A001/A002/A003"

Как заменить? Вручную?

2) Повторите п.1 только вместо замен используйте результат п.1
Получится несколько столбцов, последний из них - то что вам нужно.

вообще не понял алгоритма

3) Удалить дубликаты.

Как удалить дубликаты?

[Григорий Боев]

1) ="/"&A1
2) Делаете п.1 (в B заменяете A на B) - получаете столбец с частичными заменами. Далее делаете то же самое, только так: в C заменяете A на B
3) Функции ПОИСПОЗ/ЗАМЕНИТЬ/ПСТР

[Ieskelahsydyv]

Григорий Боев, а как из ячейки с заменами как пример A002/A009/A010/...A00N
сВПРить (формулой не макросом) каждое значение и если оно есть на складе (остаток больше 0) вывести замены которые есть на складе через "/" (как пример A002/A010)?