Страницы: 1
RSS
Вывести без дубликатов все возможные варианты замен., поиск по наличию слова в ячейке
 
День добрый, уважаемые гуру Excel. Каждый день сталкиваюсь с рутинной задачей работы подбора замен номеров.
Есть исходная таблица со всеми возможными артикулами без дубликатов.
Есть вторая таблица в которой есть замены.
Как сделать общую таблицу с 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

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


Помогите решить задачу, т.к. перебрал все возможные варианты нет результата.
Изменено: Aleksei Vidin - 11.11.2021 17:50:06
 
, почему нигде в результате нет  A00555
Изменено: Mershik - 11.11.2021 11:00:44
Не бойтесь совершенства. Вам его не достичь.
 
Оно пропускается т.к. его нет в 1 колонке с артикулами. Но его я вставил т.к. при поиске допустим есть A005 и есть A00555 это 2 разных числа, поэтому я думаю нужно использовать критерии
[@АРТИКУЛ]             "*"&"/"&[@АРТИКУЛ]                "*"&"/"&[@АРТИКУЛ]&"/"&"*"                      [@АРТИКУЛ]&"/"&"*"
 
Цитата
Aleksei Vidin написал:
т.к. его нет в 1 колонке с артикулами
Ну и что? Вы же сами сказали
Цитата
Aleksei Vidin написал:
вывести, без дубликатов, все возможные варианты замен
. А A00555 - один из вариантов замен.
 
Его нет в исходном столбце АРТИКУЛ, у A00555 нет замен, просто есть артикула A001 и A0011 это разные артикула и при поиске A001 не должен находиться A0011, поэтому A00555 был вставлен как пример при поиске A005
 
Цитата
Aleksei Vidin написал:
Его нет в исходном столбце АРТИКУЛ, у A00555 нет замен
И не должно - так как он - ЗАМЕНА! При поиске A005 A00555 находиться не должен, но, так как он - ЗАМЕНА, он ДОЛЖЕН присутствовать в списке всех вариантов замен! Вам так не кажется?
Изменено: Неопытный_Экселист - 11.11.2021 14:02:04
 
Если в 1 таблице его нет и во 2 таблице он существует в единичном варианте следовательно связей на него нет и его нет в итоговой таблицы
 
Заготовка, можно сделать UDF или макросом заполнить/сгенерить таблицу.
Только сортировки нет.

Код
Sub Perebor() 'коллекция в словаре
    Dim a, i&, t, Dic As Object
    Dim el, elel, col
    
    a = Range("C2", Cells(Rows.Count, "A").End(xlUp)).Value
    Set Dic = CreateObject("Scripting.Dictionary")
    
    On Error Resume Next
    With Dic
        .CompareMode = 1
        For i = 1 To UBound(a)
            For Each t In Split(a(i, 3), "/")
            For Each el In Split(a(i, 3), "/")
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add el, el
            Next
            Next
        Next
        
    End With
    
     For Each el In Dic.keys
        For Each col In Dic.Item(el)
        For Each elel In Dic.Item(col)
            Dic.Item(el).Add elel, elel
        Next
        Next
    Next
    On Error GoTo 0
    
    For Each el In Dic.keys
        Debug.Print "Артикул " & el
        For Each col In Dic.Item(el)
            If col <> el Then Debug.Print "Замена " & col
        Next
    Next

End Sub

 
Hugo, променял наш форум на телеграм ((( Редко появляешься ((, а мы же скучаем (
Изменено: New - 11.11.2021 18:42:23
 
Ага. Там можно с мобилы сидеть :)
 
Вставляю в макрос запускаю он выкидывает. Как заставить данный код работать
Изменено: IeskelaHsydyv - 11.11.2021 18:52:52
 
Выкидывать может на маке... Но и там можно заставить.
 
Еще раз попробовал ничего не происходит, что я не так делаю...не могу понять
 
Вообще-то происходит, но смотреть нужно в окно отладки, я ведь написал что это заготовка.
И код лучше располагать в стандартном модуле, хотя работает и из модуля листа.
P.S. Я взял один из древних своих кодов из загашника, и чуть его дополнил, поэтому он конечно довольно топорный, но работает.
Чтоб реально его применять - нужно бы сперва разобраться как/где вообще на практике это нужно?
Изменено: Hugo - 11.11.2021 19:09:51
 
Цитата
Hugo написал:
Вообще-то происходит, но смотреть нужно в окно отладки, я ведь написал что это заготовка.
Увидел, результат, мне кажется он верно перебирает, сортировка не важна главное чтобы без дублей и через / с выводом в таблицу

Замена A009
Замена A010
Артикул A003
Замена A002
Замена A001
Замена A009
Замена A010
...

используется для формирования заказа товаров, функция впр вытягивает из колонки замены соответствующие артикулам указанных в заказе и вставляет их в колонку замены в заказе и сейчас вручную просматриваются замены на наличие на складе, но планируется функцией перебирать замены указанные через "/" и выводить актуальную замену с необходимым колвом на складе.
 
Да просто можно вместо вывода в окно отладки сделать вывод на чистый лист. В два столбца как в примере, или в кучу столбцов правее сколько есть на замену.
И далее с этого листа уже можете тянуть ВПРой.
Можно сделать типа ВПР уже к готовому списку, чтоб заполнял диапазон.
Можно сделать UDF, т.е. применять как функцию с двумя или тремя параметрами.
Вы пока так и не описали что именно нужно ПРАКТИЧЕСКИ.

P.S. Да и сортировку можно приладить, но она будет воровать время, а если там 100000 строк то будет заметно.
Но если применять в UDF и сортировать нужно только то, что конкретно выводится в одну ячейку, то можно и посортировать.
Изменено: Hugo - 11.11.2021 19:38:07
 
Цитата
Hugo написал:
Вы пока так и не описали что именно нужно
Объясняю практически:
1) Есть склад с запчастями у каждой запчасти есть артикул, наличие выгружается ексель файлом
2) Есть файл ексель список замен артикулов порядка 100 000
3) Есть заказ от клиента со списком необходимых запчастей.

Сейчас перебор актуальных замен артикулов происходит в ручном режиме через Ctrl+F, каждый артикул ищется и проверяется с наличием на складе.
Может быть заказ быть на A001 на 10 шт. и в наличии есть A001 5 шт. и 5 шт. есть A008. Так вот если A008 не найти то он так и останется лежать на складе.

Поэтому и необходимо всего 2 колонки.
1 это все производившиеся когда либо артикулы без дублей отсортированы по алфавиту
2 все возможные варианты замен через "/"
3 это из 3 таблицы с заказом функцией впр найти ячейку с заменой и в ней перебрать все артикула и которые есть на складе вывести в таблицу заказа
Изменено: IeskelaHsydyv - 11.11.2021 19:46:22
 
Ну тогда наверное вариант 2: Можно сделать типа ВПР уже к готовому списку, чтоб заполнял диапазон.
Или сразу идти дальше и искать наличие по складу первой детали, если мало то ищем вторую по замене и т.д.

P.S. Изменил в файле код чтоб он заполнял вторую таблицу.
Изменено: Hugo - 11.11.2021 19:56:48
 
Или вот так - в том файле пишите в E2 и E3
A001
A005
выделяете их, выполняете код

Код
Option Explicit

Sub Perebor2() 'коллекция в словаре
    Dim a, i&, t, Dic As Object
    Dim el, elel, col, s$, c As Range
     
    a = Range("C2", Cells(Rows.Count, "A").End(xlUp)).Value
    Set Dic = CreateObject("Scripting.Dictionary")
     
    On Error Resume Next
    With Dic
        .CompareMode = 1
        For i = 1 To UBound(a)
            For Each t In Split(a(i, 3), "/")
            For Each el In Split(a(i, 3), "/")
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add el, el
            Next
            Next
        Next
         
    End With
     
     For Each el In Dic.keys
        For Each col In Dic.Item(el)
        For Each elel In Dic.Item(col)
            Dic.Item(el).Add elel, elel
        Next
        Next
    Next
    On Error GoTo 0
     

For Each c In Selection.Cells
s = "": el = Trim(c)
    If Dic.exists(el) Then
        For Each col In Dic.Item(el)
            If col <> el Then s = s & "/" & col
        Next
    Else
        s = "/Нет замен"
    End If
        c.Next = Mid(s, 2)
Next
End Sub

Изменено: Hugo - 11.11.2021 20:23:09
 
Цитата
Hugo написал:
Изменил в файле код чтоб он заполнял вторую таблицу.
Что вот так просто то, что делали вручную кучу времени, можно вот так за один проход сделать. Это гениально просто. Как я вам благодарен.
Я не понимаю неужели за столько времени никто не сталкивался с такой задачей, ведь работа с замена нужна всегда. В интернете перебрал массу вариантов.
 
Я там дописал код который заполняет замены только для нужных выделенных кодов. Коды должны быть рядом в столбце, любом.
Кстати если код оставить в модуле листа, то можно доработать (а может и так взлетит) для использования/заполнения диапазона в любом другом листе или даже файле.
Т.е. есть база замен с этим кодом, и его применяете к любому другому клиентскому файлу с кодами, к которым нужно подобрать замены.

P.S. Проверил, работает и в другом файле вот прямо как есть.
Но чуть изменил код выше чтоб обрабатывало случаи когда вписан код для которого в базе нет информации по замене.
Изменено: Hugo - 11.11.2021 20:24:10
 
Цитата
Hugo написал: Проверил, работает и в другом файле вот прямо как есть.
Вообще все четко работает. 1000кратное спасибо.
Теперь осталась вторая задача, поиск по наличию слова в ячейке, если есть запрос артикула A001 из ячейки с заменами A002/A009/A010/...A00N сВПРить (формулой не макросом) каждое значение и если оно есть на складе (остаток больше 0) вывести замены которые есть на складе A002/A010.
З.xlsm (26.91 КБ)

P.S.
На простом примере все работает отлично, но загрузив все 100000 замен в данный файл уже 5 часов Excel не отвечает, не видно что сейчас происходи или реально завис или вычисляет долго, но по загрузке ПК видно что как будто идут вычисления.
 
Уважаемые форумчане, помогите пожалуйста добить вопрос. Hugo предложил отличный макрос который выполняет обработку, но при его внедрении столкнулся с проблемами, при обработке более 1000 строк макрос уходит в зависание. Вставлял функцию DoEvents нет реакции, хотел внедрить макрос визуализации работы при помощи прогресс-бара, чтобы понять на какой стадии находится вычисления, результата нет все зависает.

Плюс выявилась ошибка:
АРТИКУЛ
A0000004711
A0000004712
A000000471267

ЗАМЕНЫ
A0000004711/A0000004712
A0000004712/A000000471267
A000000471267/A000000471167

РЕЗУЛЬТАТ вычислений
A0000004711            A0000004712/A000000471267
A0000004712            A0000004711/A000000471267/A000000471167
A000000471267       A0000004712/A000000471167/A0000004711

пропускает A000000471167, где-то ошибка

Должен получиться
A0000004711           A0000004712/A000000471267/A000000471167
A0000004712           A0000004711/A000000471267/A000000471167
A000000471267       A0000004712/A000000471167/A0000004711
Изменено: IeskelaHsydyv - 15.11.2021 10:13:12
 
Ошибка в данных, для этого кода нужны данные как в примере - в третьем столбце должны быть артикулы из первого.
Ну или нужно менять как-то код.
По зависанию не знаю, у меня не завис.
 
Цитата
Hugo написал: в третьем столбце должны быть артикулы из первого.
Вы сказали, можно в любом месте справа выводить результат, поэтому 3 столбец убрал, вывожу справа от исходного столбца. Но попробовал вставить в пример в 3 столбец, также выделяю, запускаю макрос и все равно теряется вот этот артикул A000000471167.
Должен получиться
A0000004711           A0000004712/A000000471267/A000000471167

Ну при маленьком кол-ве данных обрабатывает но стоит загрузить порядка 50000 уходит в зависание. Пример прикрепить не могу т.к. по размеру не пропускает
Страницы: 1
Наверх