Страницы: 1
RSS
Подставить ТМЦ по типу с одной таблицы в другую, чтобы штрих-коды не повторялись
 
Добрый день!

Уважаемые Форумчане и Гуру Excel, помогите!
Все перерыл в поисковиках, но не могу приделать отдельные примеры к своему макросу... :(

Суть вопроса: Есть выгрузка из базы 1С (первый лист) и наша инвентаризационная опись (второй лист).

На данный момент сделал следующее - "макрос 1" (левая картинка на первом листе сверху) выполняет сверку между таблицами и дописывает в желтые столбцы найденные позиции и данные по ним в обе таблицы, также макрос подставляет унифицированные данные (тип ТМЦ и адрес) в дополнительные столбцы.

"Макрос 2" (правая картинка на первом листе) должен делать следующее:
При нажатии происходит поиск в таблице 2 (второй лист) и подстановка значений в таблицу 1 (первый лист) по "склеенному" значению строки адрес & тип ТМЦ. В последние два столбца подставляется штрих-код и оригинальное наименование ТМЦ из второй таблицы.

Проблема!: В столбец P (штрих-коды) и в столбец Q (наименование ТМЦ) подставляется одинаковое значение по первой (или последней?) найденной строке во второй таблице.

Что требуется:
Подставлять в столбцы P и Q первой таблицы уникальные значения ТМЦ со второй таблицы (штрих-код из столбца B и наименование из столбца E) и маркировать их во второй таблице проставляя старый штрих-код и полное наименование ТМЦ (в столбцы M и N листа 2) из первой таблицы (из столбцов D и B).

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

Заранее благодарю всех откликнувшихся!

Файл весит 330Кб (не могу сократить без потери функционала) и доступен по ссылке: https://cloud.mail.ru/public/CTqT/jxkFom4Uz
Изменено: Shade31337 - 20.03.2017 17:57:32 (Вложение ссылки на файл.)
 
Кросс:
http://www.excelworld.ru/forum/10-32878-1
Я сам - дурнее всякого примера! ...
 
Обращаюсь за помощью первый раз, вопрос срочный... Вроде по правилам форума кросс не запрещен.
В дальнейшем не буду так делать :)
Изменено: Shade31337 - 20.03.2017 19:37:11
 
Брр...
Скажите понятно - по какому ключу сопоставлять таблицы (номер/буква столбца) и из каких столбцов куда копировать. И что делать если есть повторы.
А вникать в эти сотни строк/столбцов/ТМЦ...
Лучше бы сделали небольшой пример, на котором можно показать сам алгоритм/механизм кода.
 
Цитата
Hugo написал:
Скажите понятно - по какому ключу сопоставлять таблицы (номер/буква столбца) и из каких столбцов куда копировать. И что делать если есть повторы.
Коротко суть:
Сравниваем столбцы N и O (значение N & O одновременно) на первом листе со столбцами K и L (K & L) на втором листе. В столбцах P и Q первого листа нужно внести штрих-коды из найденной строки во второй таблицы (штрих-код из столбца B и наименование из столбца E). Соответственно во вторую таблицу в столбцы M и N подставить соответствующие значения из столбцов D и B первой таблицы.
Главное чтобы после нахождения в первой таблице строки из второй (и внесения дополнительных значений в таблицы) эти строки больше не использовались в сопоставлении. Т.е. новые штрих-коды из второй таблицы не использовались по 2 раза.

Надеюсь все понятно объяснил, короче не получается... :(
Файл попробую сократить чуть позже.
Спасибо.
 
Для начала, развивайте копированием чего-то куда-то. И покраской.
Код
Option Explicit

Sub tt()
    Dim a, i&, t$
    Dim dic1 As Object, dic2 As Object

    Set dic1 = CreateObject("scripting.dictionary"): dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary"): dic2.comparemode = 1

    a = Sheets(1).[a4].CurrentRegion.Columns(4).Value
    'заголовок - 2 строка массива, т.е. на листе +3
    For i = 3 To UBound(a): dic1.Item(Trim(a(i, 1))) = i + 3: Next

    a = Sheets(2).[a1].CurrentRegion.Columns(2).Value
    'заголовок - 1 строка массива, на листе +0
    For i = 2 To UBound(a): dic2.Item(Trim(a(i, 1))) = i: Next

    For i = 2 To UBound(a)
        t = a(i, 1)
        If dic1.exists(t) Then
            Debug.Print "Ключ второго листа " & t & " из строки " & i & " находится в строке " & dic1.Item(t) & " листа 1"
        Else
            Debug.Print "Ключа второго листа " & t & " нет в первом листе!"
        End If
    Next
End Sub


P.S. Важно - там не совпадают форматы ключей, в первом листе они числовые, во втором текст. Т.е. в первом листе прощай ведущие нули...
Изменено: Hugo - 20.03.2017 22:01:39
 
Я успел накидать код до получения описания :)
По факту код не подходит, но можно взять за основу :)
Цитата
Shade31337 написал:
Сравниваем столбцы N и O (значение N & O одновременно) на первом листе со столбцами K и L (K & L) на втором листе
Т.е. десяток
Калининградская обл, Калининград г, Театральная ул, д. 35 ИБП
сраниваем с десятком
Калининградская обл, Светлый г, Советская ул, д. 5/3 Телефон
и копируем первые попавшиеся кривые штрихкоды без пропавших ведущих нулей?
Можно на каждый ключ собрать в словаре коллекцию штрихкодов, затем при цикле по другому листу из этих коллекций выбирать по значению, уменьшая коллекцию ключа...

P.S. Не, со второго листа можно копировать коды, там нули на месте.
Изменено: Hugo - 20.03.2017 22:11:47
 
На самом деле все проще:
все "Калининградская обл, Балтийск г, Ленина пр-т, д. 22б ИБП" на одном листе сравниваем со всеми "Калининградская обл, Балтийск г, Ленина пр-т, д. 22б ИБП" (они специально унифицированы для сверки) на втором листе и тупо копируем штрих-коды со второго листа на первый и с первого листа на второй (новый и старый ш-к), пропуская соответственно строчки, где ш-к уже найдены. Если ИБП на первом листе больше, то лишние остаются без новых ш-к из второго листа (и наоборот со вторым листом).
Изменено: Shade31337 - 21.03.2017 22:53:08
 
В идеале, после отсева обоих таблиц по одинаковым адресам и типам ТМЦ, отсеять по разным адресам в пределах города, затем области и региона...
Но главное понять как принципиально сделать предыдущий шаг.

Если все получится - с меня на пивко...
 
Цитата
Shade31337 написал:
Сравниваем столбцы N и O (значение N & O одновременно) на первом листе со столбцами K и L (K & L) на втором листе. В столбцах P и Q первого листа нужно внести штрих-коды из найденной строки во второй таблицы (штрих-код из столбца B и наименование из столбца E).
Цитата
Shade31337 написал:
Главное чтобы после нахождения в первой таблице строки из второй (и внесения дополнительных значений в таблицы) эти строки больше не использовались в сопоставлении. Т.е. новые штрих-коды из второй таблицы не использовались по 2 раза.
Это вроде как сделал, но не проверял досконально:
Код
Option Explicit

Sub tt()
    Dim a, b, i&, t$
    Dim dic1 As Object, col As New Collection

    Set dic1 = CreateObject("scripting.dictionary"): dic1.comparemode = 1

    a = Sheets(2).[a1].CurrentRegion.Columns(11).Resize(, 2).Value
    b = Sheets(2).[a1].CurrentRegion.Columns(2).Resize(, 4).Value
    'заголовок - 1 строка массива, на листе +0
    With dic1
        For i = 2 To UBound(a)
            t = Trim(a(i, 1)) & "|" & Trim(a(i, 2))
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add b(i, 1) & "|" & b(i, 4)
        Next
    End With

    a = Sheets(1).[a4].CurrentRegion.Columns(14).Resize(, 2).Value
    'заголовок - 2 строка массива, т.е. на листе +3

    Application.ScreenUpdating = False
    With Sheets(1)
        For i = 3 To UBound(a)
            t = Trim(a(i, 1)) & "|" & Trim(a(i, 2))
            If dic1.exists(t) Then
                Set col = dic1.Item(t)
                If col.Count Then
                    .Cells(i + 3, 16).Resize(, 2) = Split(col(1), "|")
                    dic1.Item(t).Remove 1
                End If
            End If
        Next
    End With
    Application.ScreenUpdating = True

End Sub

Проверил слегка - вроде ОК
Изменено: Hugo - 20.03.2017 22:52:12
 
Hugo, все работает отлично! Спасибо огромное (пыво точно твое), можно еще на закусь поспрашивать?

Поиск не учитывает уже заполненные строки в столбце J (которые нашлись предыдущим макросом по штрих-коду) -
их нужно исключить из обработки. Также нужно добавить старые штрих-коды и наименования ТМЦ из первой таблицы во вторую.

Как еще можно оптимизировать полотно ниже (25 минут обрабатывает 20000 строк :()?

Скрытый текст

И последний штрих будет сравнение в пределах одного города и области.

Все лишнее удалил из файла и закинул во вложение. Макрос твой кинул на кнопку.
 
Цитата
Hugo написал:
Сравниваем столбцы N и O (значение N & O одновременно) на первом листе со столбцами K и L (K & L) на втором листе
Пардон, один момент нашел - код сравнивает значения только по типу ТМЦ без учета полного адреса. Это важно, т.к. ТМЦ может находиться в другом регионе и заменить ему ш-к можно только в пределах области\города.
 
Столбец J с кодами мой макрос вообще никак не касался, поэтому по этому вопросу ничего не скажу.
Цитата
Shade31337 написал:
Также нужно добавить старые штрих-коды и наименования ТМЦ из первой таблицы во вторую.
- что это и куда тоже изучать недосуг, поздно уже...
Про "полотно" аналогично - проще переписать с нуля, если ясна задача, чем эту простыню вычитывать.
Так что я часов так на 20 пас, до завтрашнего вечера.
 
Цитата
Hugo написал:
Так что я часов так на 20 пас, до завтрашнего вечера.
Ок, я утром тогда максимально коротко и ясно опишу суть полотна и задачи по файлу в целом.
Если получится все переписать (там примитивный код в полотне) и добиться результата - с меня 1к руб.
Устроит? Сможешь вечером завтра помочь?  
 
Хорошо, завтра по Москве около 21 посмотрю.
 
Цитата
Hugo написал:
Хорошо, завтра по Москве около 21 посмотрю.
Полная задача для макроса:
1 этап.

·         В столбце D (лист 1) и столбце B (лист 2) добавить «0» перед числом и привести столбцы к единому варианту, который можно корректно сравнивать (текстовый).

2 этап.

·         Сравнить столбец D (лист 1) и столбец B (лист 2). В результате сравнения добавить найденные значения строк из столбцов B, C, E, F (лист 2) в столбцы J, K, L, M (лист 1) соответственно.

·         По аналогии добавить найденные значения строк из столбцов D, G, B, H (лист 1) в столбцы G, H, I, J (лист 1) соответственно.

·         При сравнении учитывать возможность отсутствия штрих-кода в строках таблиц, либо наличие записей, отличный от цифр (пример: «б.н.», «без ш.к.», «-»).

3 этап.

·         Из листов 3 и 4 внести соответствия по адресам и типам ТМЦ в столбцы N и O листа 1 (сравниваем столбцы B и H с листом 3 и 4 соответственно).

·         По аналогии внести соответствия из листов 5 и 6 по адресам и типам ТМЦ в столбцы K и L листа 2 (сравниваем столбцы K и L с листом 6 и 5 соответственно).

4 этап.

·         Сравниваем столбцы N и O (значение N & O одновременно) на первом листе со столбцами K и L (K & L) на втором листе. В столбцах P и Q первого листа нужно внести штрих-код и наименование ТМЦ из найденной строки во второй таблицы (штрих-код из столбца B и наименование из столбца E листа 2). Заполненные строки в столбце J листа 1 (которые нашлись предыдущим макросом по штрих-коду) нужно исключить из обработки. Также значения штрих-кодов не должны повторяться.

·         После внесения значений в столбцы P и Q первого листа, в столбцы M и N второго листа вносим наименование ТМЦ и старый штрих-код найденной строки из первой таблицы (столбцы B и D соответственно).

5 этап (последний).

·         Обрезаем значение строк в столбцах N листа 1 и K листа 2 до города, т.е. запись «Калининградская обл, Калининград г, Батальная ул, д.39-45» должна превратиться в «Калининградская обл, Калининград г», выносим значения (если необходимо) в отдельный столбец обоих таблиц на листе 1 и 2.

·         Запускаем еще один цикл сверки по аналогии с 4 этапом, учитывая ранее найденные позиции. Добавляем новые столбцы с ш-к и ТМЦ в таблицы.

Файл весит 330Кб и доступен по ссылке: https://cloud.mail.ru/public/CTqT/jxkFom4Uz

 
Написал в личке - есть вопросы почти по всем этапам.
Сделал 3-й этап - по нему вроде всё понятно :)
Ну и часть 4-го тоже готова, с чего начиналось.
 
Цитата
Hugo написал:
Написал в личке - есть вопросы почти по всем этапам.
Ответил по всем вопросам в личку. Оставил свой контакт.
Страницы: 1
Наверх