Уникальная консолидация данных в нескольких рабочих листах

У меня есть шесть рабочих таблиц, которые я хочу взять с уникального идентификатора из определенного столбца и объединить их в 1 мастер (чтобы сделать некоторый анализ и другое представление данных).

Все данные начинаются с одной и той же ячейки C17, но заканчиваются в разных строках (например, C180, C268 и т. Д.). Я хочу иметь возможность консолидировать уникальные идентификаторы из всех шести таблиц еженедельно.

Есть ли решение, которое не будет использовать формулы массива, поскольку это СЕРЬЕЗНО вызывает проблему из-за большого количества строк и ресурсов, необходимых для вычисления списка. Автоматизация VBA предпочтительнее, когда диапазоны ячеек для консолидации могут быть динамическими, а имена листов ссылаются на конкретные ячейки в основной листе (она никогда не будет удалена или изменена в пределах шести других)?

Итак, я бы запускал макрос, который объединит все данные, основанные либо на именованных диапазонах, либо на определенных ячейках с именами и диапазонами листов в них (используя косвенные для использования этих строк) и вставляя их в новый диапазон.

UDF тоже будет приемлемым, я просто не хочу, чтобы Excel «замораживал» выполнение вычислений.

BTW, я прочитал « Получение уникальных значений в Excel, используя только формулы», но эти решения работают только в том случае, если данные находятся на одном листе или в очень специфических условиях. Также формулы массива не будут работать эффективно, так как мои данные имеют буквально тысячи строк.

Редактировать:

Вот тестовый макрос, который я использовал для получения данных с одного листа, но проблема в том, что я не могу использовать динамику и не добавлять остальные имена из других листов, потому что диапазон, в который я его копирую, не могу вытащить первую пустую ячейку после копирования id.

Sub ConsolidateDATA() 'yStr = Evaluate("=ADDRESS(MIN(IF($C$10:$C$9999 = "", ROW($C$10:$C$9999))), 3, 1, 1)") 'Attempted dynamic range copy ^ - failed yStr = "C10" Range("Sheet1!$B$5:$B$29").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Range(yStr), Unique:=True End Sub 

У меня также были успешные попытки с формулами массива, но, к сожалению, они ресурсоемкие, что они ДЕЙСТВИТЕЛЬНО плохие решения.

– Формула массива для объединения списков в 1 мастер

 =IFERROR(INDEX(INDIRECT($B$6, TRUE), ROWS(B$13:$B14)), IFERROR(INDEX(INDIRECT($B$7, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE))), IFERROR(INDEX(INDIRECT($B$8, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7))), IFERROR(INDEX(INDIRECT($B$9, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8))), IFERROR(INDEX(INDIRECT($B$10, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE))), IFERROR(INDEX(INDIRECT($B$11, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE)) - ROWS(INDIRECT($B$10, TRUE))),IFERROR(INDEX(INDIRECT($B$12, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE)) - ROWS(INDIRECT($B$10, TRUE)) - ROWS(INDIRECT($B$11, TRUE))),""))))))) 

– Формула массива для получения только уникальных данных

 =INDEX(TotalNameListRangeFromFormulaAbove, MATCH(0, COUNTIF($D$16:D16, TotalNameListRangeFromFormulaAbove), 0)) 

Я думаю, что сочетание петель и коллекций может решить вашу проблему 🙂

http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-collections-in-excel-vba/

  for i = 1 to UBound(worksheetcount, 1) for j = 1 to UBound(cellrangecount, 1) With CreateObject("scripting.dictionary") For Each "Key" In cellrangecount(cellrangecount) If Not .Exists(Key) Then .Add Key, Key & "_content" Next j next i End With 

Я считаю, что это достаточно хорошо, чтобы вы начали по правильному пути. В итоге я использовал словарь вместо коллекции, но вы можете изменить это, если хотите. Только незначительные различия в декларациях и добавлении, но по существу одинаковые (анекдотически, есть пара огромных различий, а не то, что я думаю, что это имеет значение здесь). Дайте мне немного времени, и я вернусь с чем-то более отполированным / законченным, чем просто «основная идея о том, как он может работать».

ссылка для словарей от того же парня (мне очень нравится, как этот парень разрабатывает вещи)

http://excelmacromastery.com/Blog/index.php/vba-dictionary/

Давайте будем гением компьютера.