Компьютерный форум

Компьютерный форум (http://www.hardforum.ru/)
-   Visual Basic (http://www.hardforum.ru/f156/)
-   -   Сравнение двух двумерных массивов (http://www.hardforum.ru/t132119/)

Iceeyes 27.08.2018 13:00

Сравнение двух двумерных массивов
 
Вложений: 1
Добрый день, уважаемые форумчане.
Я столкнулся с проблемой и уже 3 день не могу никак прописать волшебную формулу по сверке двух массивов:281:.
Посмотрите, пожалуйста, вложенный файл. Мне нужно сравнить два списка: Acrrued (начисленные) с Used (использованные). В списке Accrued показаны данные по начисленным ваучерам. В списке Used показаны люди, которые использовали ваучер/ваучеры. В листе колонка “New formula” показывает данные и сравнивает со списком Used. К сожалению, данная формула неидеальна, т.к. функция ВПР только доходит до первого ключевого совпадения, проверяет его, и дальше по остальному списку уже не ведет проверку. Я пробовал что-то придумать с помощью MATCH, INDEX, но не получилось. Мне кажется, что по тем условиям, которые даны, может справиться лишь макрос с использованием циклов (двумерные массивы). Хотя возможно у меня просто недостаточно знаний по Excel и действительно можно обойтись без макроса. Условия следующие:
В листе Accrued описаны начисленные ваучеры, их надо сравнить со списком Used и в колонке G прописать в каждой строке итог, было ли найдено совпадение или нет в списке Used, поставить Yes – если найдено и No – если не найдено.
Список Accrued можно сравнить с Used по следующим критериям: «Customer Number», «Accrued date», «Amount». Такие же поля есть в списке Used: “Customer Number”, «GL Date», «Foreign Currency Debits» (все поля выделил одинаковым синим цветом). Поле Customer number уникальное, но проблема в том, что у одного и того же человека, есть несколько начисленных ваучеров на точно такую же или иную сумму. Вы можете увидеть такие совпадения, они выделены желтым цветом в листе Used. Поле GL date всегда должно иметь дату равную или более позднюю от даты «Accrued date». То есть оно должно быть по условию: GL date>=Accrued date.
Для самопроверки, если вы все же пропишите макрос и после его выполнения поля в листе Accrued колонки G (оранжевый цвет) будут заполнены. Отфильтруйте эту колонку по “Yes” , то есть по использованным ваучерам. Общая сумма должна будет равна сумме 5 719 740.00. Вы не подумайте, что формула в колонке “New formula”, если сразу отфильтруете по этому полю. Мне пришлось прописать руками Yes в местах, где Customers имеют по 2 ваучера, как например в ячейке F27. Там ручной ввод.
В любом случае спасибо, что обратили внимание. Условия действительно непростые, возможно задача даже нерешаемая ввиду малых ключевых условий. Заранее благодарен!

Печальный тип 01.09.2018 05:14

А каждому новому начисленному ваучеру можно придать свой номер?
И этот номер указывать при использовании во 2 листе Used. Тогда задача упростилась бы, и поля совпадение номеров клиента, даты и суммы уже не имела никакого значения.

Печальный тип 01.09.2018 08:08

vb Код:
Sub find()
Dim i, j, n
Dim a1 As Worksheet, a2 As Worksheet
Set a1 = Sheet1
Set a2 = Ëèñò1
n = a1.Cells(1, 8)
If n <= 1 Then
    MsgBox "Укажите в ячейке H1  номер строки с которой начать проверку"
    GoTo back:
End If

For i = n To a1.UsedRange.Rows.Count
    a1.Cells(i, 8) = "No"
    For j = 4 To a2.UsedRange.Rows.Count
        If a1.Cells(i, 1) = a2.Cells(j, 3) And a1.Cells(i, 3) <= a2.Cells(j, 12) And a1.Cells(i, 4) = a2.Cells(j, 15) Then
            a1.Cells(i, 8) = "Yes"
            Exit For
        End If
    Next j
Next i
back:
End Sub


Моих мозгов хватает лишь на такой макрос...
1) не идет опять же исключения, в случае повторения, можно в принципе это доделать если посидеть повтыкать, но проще задать порядковый номер или добавить дополнительное поле, где делать отметку для самого макроса о уже использованных строках или окрашивать их
2) то ли мой комп помирает, то ли дело в коде, но полностью обрабатывать все строки с 1 листа не получается, поэтому в первом цикле я брал первые 100 строк, иначе он зависает в тяжести поставленной задачи.
3) нужно указывать с какой строки листа Accrued начать сравнивать с листом Used. Поможет ускорить работу, чтобы при добавлении новых строк не требовалось по новой сравнивать старые результаты.

Iceeyes 03.09.2018 15:19

Цитата:

Сообщение от Печальный тип (Сообщение 874321)
А каждому новому начисленному ваучеру можно придать свой номер?
И этот номер указывать при использовании во 2 листе Used. Тогда задача упростилась бы, и поля совпадение номеров клиента, даты и суммы уже не имела никакого значения.

ПО каждому начисленному ваучеру есть свой уникальный номер, НО (жирное НО), нет привязки никакой между этим номером указанным в колонке "Check Number" листа Accrued и всеми ячейками листа Used.
Ключевые столбцы, по которым можно проверить условия выполненной задачи выделены синими ячейками в двух листах и мне пришлось в голову распознать их только по привязке: Customer Number, сумма и дата.
В любом случае, большое Вам спасибо, что выделили время и не остались в стороне. Думаю, mission impossible, т.к. нужны еще доп. условия.
К сожалению, Ваш код мне не удалось запустить из-за ошибки при компиляции: Object required.

Печальный тип 04.09.2018 18:55

Вложений: 1
немного переделал, добавил к коду комменты.
В общем мы просматриваем каждый ваучер с листа Used по 3 полям указанными вами со всеми записями из листа Accrued. При совпадении на листе Accrued делаем пометку "yes" и окрашиваем в зелёный цвет и в листе Used тоже ставим пометку "yes". В ином случае ничего не пишем(Если окраска не нужна то можно спокойно убрать строчку окраски).
Тот список что вы дали у меня обработал где-то за 1 минуту. При добавлении новых записей будет уходить меньше времени, т.к. он будет пропускать записи с поля Label на листе Used с пометкой "Yes". Если конечно своевременно отмечать, скажем через каждые 10 записей.

В общем смотрите, кнопка на листе Accrued и рядом ячейка с проверкой суммы.

Iceeyes 11.09.2018 15:55

Цитата:

Сообщение от Печальный тип (Сообщение 874469)
немного переделал, добавил к коду комменты.
В общем мы просматриваем каждый ваучер с листа Used по 3 полям указанными вами со всеми записями из листа Accrued. При совпадении на листе Accrued делаем пометку "yes" и окрашиваем в зелёный цвет и в листе Used тоже ставим пометку "yes". В ином случае ничего не пишем(Если окраска не нужна то можно спокойно убрать строчку окраски).
Тот список что вы дали у меня обработал где-то за 1 минуту. При добавлении новых записей будет уходить меньше времени, т.к. он будет пропускать записи с поля Label на листе Used с пометкой "Yes". Если конечно своевременно отмечать, скажем через каждые 10 записей.

В общем смотрите, кнопка на листе Accrued и рядом ячейка с проверкой суммы.

Добрый день!
Большое Вам СПАСИБО! Очень интересный макрос, никак наиграться не могу :-) Ура! :288:


Часовой пояс GMT +4, время: 14:20.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.