Dynamic Formula for Getting Unique Items from a list and sort them alphabetically

I already have the solution, but it seems way too cumbersome for something so simple and as a result lags the entire worksheet quite heavily.

Here is the (array) formula:

Please Login or Register  to view this content.

The cell H1 contains a list of years. There are a bunch of worksheets labeled COURSES-XXXX (the year).

The column B:B in said list contains the information I want.

Is there a better way of achieving this?

The spreadsheet contains information I cannot give out, and there is too much interlinked that taking some thing out would only break other parts.

I will fix the above code and take out the dynamic portion.

Please Login or Register  to view this content.

Originally Posted by XOR LX

It doesn't have to be your actual workbook. A smaller, mocked-up version with dummy data will suffice, providing it illustrates the same issue.

Regards

I know, but it really is too much trouble. What I have works, but I'm trying to take away things that compromise loading time. Still, I'll see what I can do.

I'm looking at this formula now (http://www.get-digital-help.com/2009...from-a-column/) and seeing if I can use something like this. It has the same core mechanics as the formula I'm using.

Well, I didn't mean it in that way.

Apologies if it was interpreted wrong. What I meant to say was: It will take me ages to strip down the worksheet to make it function similarly to what it is now and replace existing data with false data in a number of places for the entire workbook to work as expected without breaking as there are about 16 to 20 dynamic formulas in a number of sheets that all work together to bring the results needed so that this one particular list (the one in question) can be worked on and simplified.

That's what I meant to say.

Hi,

Okay, I managed to simplify it with the help of another site.

Right now the code looks like this:

Please Login or Register  to view this content.

Removing the dynamic portion will be like:

Please Login or Register  to view this content.

This works like a charm and avoids blanks (if you've got them).

Thanks!

Originally Posted by XOR LX

I appreciate that. But then I'm sure you also appreciate that it's practically impossible for us to take the formula you gave and work on it. The only feasible way to put that formula into context would be for us to go to the effort of effectively creating a workbook which resembles your actual one so that we can test potential solutions.

You mention a link to a site. As a compromise, then, why can't you attach a workbook with some dummy data which, although not containing all the links, etc. as in your real workbook, at least illustrates a similar idea to that which you are trying to achieve. True, you might then have to work a little on adapting any solution given to your actual workbook, but then I can't see any other way that this is going to be achieved.

Regards

The main issue is the list being called from two dynamic list arrays and formulate a single list in the course. This has to do with whether the country in question is stable or not, which is why I can't forward the data along. That query is taken from a bunch of other queries and it just compounds itself into a messy, very heavy worksheet.

I can do it, it would just take a long time to do.

It works now, so I'm sorry for troubling you guys and feel quite awful about saying 'too much trouble'. I can understand how that would be taken out of context and wasn't my intention to be rude, blase, or arrogant (despite the comment magically pulling off all three). But don't worry, I will most likely be back with another unreasonable demand in the future.

Thanks for trying to help. I appreciate it.