Get multiple columns of unique values based on cascading selection criteria (drop boxes)

Hello,

I have a workbook that has 5 columns of information with ~25K rows of unique value combinations. The information in the columns is as follows:

Col A: location of work (Location)
Col B: Sub-location within the location of work (Sublocation)
Col C: What the person is working on (component)
Col D: What the issue is (TypeofFailure)
Col E: How the issue was corrected (CorrectionMethod)

*brackets are named ranges

I have 5 drop boxes in column B2:B6 to select this information and as the user begins to populate the dropboxes (via a userform) starting in sequence with Location, the subsequent drop-boxes should only show the values applicable to the value chosen in each of the previous drop boxes (cascading down). The location of work (Col A) only has 16 unique static values so i don't need to use a formula/macro for the data in column A.

I'm currently using a series of arrays to complete this for me, but as everyone knows, array calculations are extremely slow; Slow enough that the userform comboboxes aren't able to load all of the "unique" values in each of the subsequent drop boxes past SubLocation because the array calculation gets more complex with each iteration.

The array calculation i'm using for Column B (sub-location) is the following:

Please Login or Register  to view this content.

The array calculation for Column E:

Please Login or Register  to view this content.

Columns N through Q are the array calculation output columns for SubLocation through CorrectionMethod.

The problem is with 5 tiers, the array calculations are pretty heavy so it runs extremely slow. Is there a quicker way to build similar arrays with VBA or a quicker way to do this in general?

I would really appreciate any input/suggestions!

Originally Posted by bakerman2

Is it possible to post a desensitized example file so we have something to work with ?

Hello Bakerman,

Apologies for the delay on posting a sample workbook. had some issues replicating everything but i think i got it working. Keep in mind that this spreadsheet only has ~50 value combinations total (instead of 25000 which is what i need this to work for) so the calculations are MUCH quicker in the sample workbook. One thing i noticed while trying out different variations in the sample workbook is that if i select any one of the options and the corresponding dropbox only has 1 applicable value, the corresponding drop box doesn't get populated. You can test this by trying Garage - Garage Door - Bolt - Cracked - Then the correctionmethod doesn't get populated and i'm not sure why.

Originally Posted by bakerman2

I've added an extra Userform with code to test.

I thought this was going to be a longshot but this works perfectly! Thank you so much bakerman! much much quicker and i'm glad i'm able to get rid of the worksheet array calculations.