Merge colums from separate sheets and output unique distinct sorted list to third sheet

So its been a few day since my first post which may have been a case of to much information. So I will approach my problem from a different angle and try to resolve one piece of the puzzle at a time.

I really expected to find the answer after extensive searches of numerous forums as this is easy to solve with a formula. I can post my working formulas if anyone wants to see them. Unfortunately the load on the system with all the formulas requires a VBA solution.

I have the following VBA. This code indexes the source sheet/column and copies a unique distinct list to the target sheet/column. Works great, automates the process and removes a bunch of very slow formulas. I change this up as needed, name, source ranges, target ranges and fire it off with a button with this code

Please Login or Register  to view this content.

As the source data is already sorted I have not needed to sort the output, but combining two columns adds that requirement.

Please Login or Register  to view this content.

How to modify this to index two columns on two sheets, and create a unique distinct list preferably sorted. Don't let the sub name "Dates" throw you. The data could be names, dates or numbers.


2to1.jpg

Any help would be appreciated as I have been searching and trying code for a while now.

I think in this instance, unless the dictionary is a requirement, that just setting ranges equal to each other is a better approach. Then just use excel's built in sort and remove duplicate features because they're there, so why not.

If you still want to use the dictionary, just set your "a" range, run the for loop, set your "a" range again, run the for loop. That would at least have the built in advantage of not having any duplicates already.

Please Login or Register  to view this content.

Your solution is brilliant. The data extraction is near instantaneous, compared to a formula that was taking 10 or 15 seconds.

You have solved what days of research was unable to answer. Many many thanks.

Would it be difficult to add a criteria to only return values from the above query that matched the designated criteria. Such as only return values where column x=Z2 for Blue?

2to1crit.jpg

Sure, but I would use a completely different approach.

Please Login or Register  to view this content.

You are a lifesaver

I am setting it up now. Is it possible to set the criteria to reference a sheet and cell number instead of "blue". I expect this variable to change as I am using the earlier VBA to populate these and other values.

Such as...

Please Login or Register  to view this content.

Sure.

Please Login or Register  to view this content.

It working great with the updated search for string. It sorts dates ok, but when I query a column with names the sort is a bit jumbled. All the data is present just not sorted correctly.

Should I use a different sort method or orientation for names?

Please Login or Register  to view this content.

I was able to correctly sort the output by selecting the sort button from the toolbar and choosing sort ascending. Anyone know if this will be persistent? Really need other users who view the workbook to see this data as sorted ascending.