+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    02-21-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    96

    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!

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,905

    Re: Get multiple columns of unique values based on cascading selection criteria (drop boxe

    Is it possible to post a desensitized example file so we have something to work with ?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    02-21-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    96

    Re: Get multiple columns of unique values based on cascading selection criteria (drop boxe

    Quote Originally Posted by bakerman2 View Post
    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.
    Attached Files Attached Files

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,905

    Re: Get multiple columns of unique values based on cascading selection criteria (drop boxe

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

  5. #5
    Registered User
    Join Date
    02-21-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    96

    Re: Get multiple columns of unique values based on cascading selection criteria (drop boxe

    Quote Originally Posted by bakerman2 View Post
    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.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,905

    Re: Get multiple columns of unique values based on cascading selection criteria (drop boxe

    Glad to be able to help.
    Thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Change Event, Move multiple rows based on drop down selection for multiple criteria
    By Webbers in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-02-2017, 11:51 AM
  2. Replies: 4
    Last Post: 05-19-2015, 11:53 PM
  3. [SOLVED] using SUMIFS and adding values based on multiple criteria based on selection from the drop
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 12:55 AM
  4. [SOLVED] cascading combo boxes and unique values
    By union in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2013, 05:38 PM
  5. Select multiple criteria based on check box selection and return all unique values.
    By TommyToe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 09:14 AM
  6. Replies: 1
    Last Post: 08-23-2012, 07:38 PM
  7. Multiple selection criteria, which formula? .. and selection based on unique numbers
    By FalkirkJim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 05:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1