Unique data consolidation across multiple Worksheets
<p>I have six worksheets which I want to take the unique id's from a specific column and consolidate them into 1 master (in order to do some analysis and different data representation).</p>

<p>The data all starts from the same cell C17, but ends at different rows (ie C180, C268, etc). I want to be able to consolidate the unique ids from all six spreadsheets weekly.</p>

<p>Is there a solution that will not use array formulas as that SERIOUSLY causes a problem due to the sheer number of rows and resources needed to calculate the list. <em>VBA automation is preferred</em> where the cell ranges for consolidation can be dynamic and the sheet names are referenced from specific cells in the master sheet (it will never be deleted or altered to the extent of the six others)?</p>

<p>So, I would run a macro which will consolidate all the data based off either a named range or specific cells with the sheet names &amp; ranges in them (using indirect to use those strings) and paste that into a new range.</p>

<p>UDF's would be acceptable as well, I just do not want Excel to "freeze" doing calculations.</p>

<p>BTW, I did read <a href="https://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only">Getting unique values in Excel by using formulas only</a> but those solutions only work if the data is on the same sheet or under very specific conditions. Also the array formulas would not work efficiently since my data is literally thousands of rows long.</p>

<p>Edit:</p>

<p>Here's a test macro I used to get data from one sheet, but the problem is I can't use dynamics nor add the rest of the names from the other sheets because the range I copy it to I can't pull the first blank cell after the id's copied.</p>

<pre><code>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
</code></pre>

<p>I have also had successful attempts with array formulas, but unfortunately they are resource intensive that they are REALLY bad solutions.</p>

<p>-- Array formula to combine lists into 1 master</p>

<pre><code>=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))),"")))))))
</code></pre>

<p>-- Array formula to get just unique data</p>

<pre><code>=INDEX(TotalNameListRangeFromFormulaAbove, MATCH(0, COUNTIF($D$16:D16, TotalNameListRangeFromFormulaAbove), 0))
</code></pre>