1

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).

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.

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. VBA automation is preferred 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)?

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 & ranges in them (using indirect to use those strings) and paste that into a new range.

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

BTW, I did read Getting unique values in Excel by using formulas only 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.

Edit:

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.

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

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

-- Array formula to combine lists into 1 master

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

-- Array formula to get just unique data

=INDEX(TotalNameListRangeFromFormulaAbove, MATCH(0, COUNTIF($D$16:D16, TotalNameListRangeFromFormulaAbove), 0))
Community
  • 1
  • 1
G.T.D.
  • 386
  • 1
  • 5
  • 21
  • What have you tried so far? Users asking for code are **expected** to show original effort. –  Feb 22 '16 at 01:52
  • Ok, no problem. I did not want to clog the original post, but I will edit it in. – G.T.D. Feb 22 '16 at 01:55
  • I am not sure why this got down voted as it does not break any rules nor did I get a chance to answer any comment requests. – G.T.D. Feb 22 '16 at 02:02
  • I down-voted and voted to close your original question because it was simply a *'gimmer teh codez'* type question with no sample data, no expected results and no code or formula showing original effort. It's not up to me to remind you that you missed all of these important factors before I choose to exercise my options. It is up to you to write a decent question to begin with. –  Feb 22 '16 at 02:14
  • As for sample sets of data and expected results, I do not think I can provide that with over complicating the question. I can provide a sample work book possibly which might work, but I am not sure what SE policies are on file sharing. – G.T.D. Feb 22 '16 at 02:19

1 Answers1

0

I think a combination of loops and collections might solve your problem :)

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

I believe this is good enough to get you started on the correct path. I ended up using a dictionary instead of a collection but you can change that if you'd like. Only minor differences on declarations and adding but essentially the same (anecdotally speaking, there are a couple of huge differences, not that I think it matters here). Give me some time and I'll return with something more polished/finished than just the "basic idea of how it might work."

link for dictionaries from the same guy (i really love the way this guy elaborates on stuff)

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

Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • 1
    Link-only answers go against the site guidelines for SO being a Q&A site - all information on the site itself, not relying on external links. It would help improve the value of your contribution if you could provide a skeletal example how your suggestion would help solve the problem. – Cindy Meister Feb 22 '16 at 16:59
  • Sorry all. I did not realise this was unacceptable. I will review and try and make an ad hoc usable template for OP – Doug Coats Feb 23 '16 at 12:25
  • @DougCoats, why do you close the _with_ structure outside the _for_ structures? – G.T.D. Feb 26 '16 at 00:16
  • there is a with statement in the createobject line. Its a habit for me when writing to automatically write if/end if, with / end with before supplying code between them. Similar to xml and html where you preemptively write etc. Unless end with isnt needed? – Doug Coats Feb 26 '16 at 14:15
  • I guess in this particular instance it isnt needed. sorry about that. more of a force of habbit i guess – Doug Coats Feb 26 '16 at 14:18