• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dependable lists with large data - Array required keeps crashing

C

cacos

Member
Hi everyone


Here's an issue I've always had and was never able to solve completely, probably quite simple for someone out there.


I'm attaching an example file. I've got 6 comboboxes where the user can input 6 different variables. And there's a list with the data (8000 rows long) with the 6 categories, each with their own variables.


So I need that each of the comboboxes is filled with a list, depending on the criteria entered on the box above. For example, user entered cars (among different vehicles), on the list below there should only appear cars. And once they select a car, only mechanic parts related to cars. And so on.


I've tried using arrays, but Excel crashes. There's just to many cells and 6 different array lists.


File is: https://docs.google.com/open?id=0B9nov_b3A5SvN3FJYk1wc1RTTEE


Thanks in advance!
 
Good Morning cacos,


Downloaded the file but not able to understand the conditions you have specified.


I understand that you are trying to create dependent drop down list but I am not able to comprehend the conditions(you have explained) to be applied.


Can you plz answer my below question?


1)Will all 6 combo boxes be dependent on each other? If yes, plz provide example for each one from your example data.


2)You have given an example of cars(as an option to select) from vehicles column, but I do not see any cars listed in that particular Col, instead etc in all cases. Plz clarify!!


3)Are you looking for only excel based approach or you are ok to go ahead with VBA?


Manwhile, you may want to look at the following thread of your interest:


http://www.get-digital-help.com/2010/10/28/apply-dependent-combo-box-selections-to-a-filter-in-excel-2007/


http://www.contextures.com/xlDataVal10.html


Regards,

Kaushik
 
Hi Kaushik ,


I think I can answer your questions :


1. The 6 categories can be labelled Cat1 , Cat2 , Cat3 , Cat4 , Cat5 , Cat6.


2. The 8000 rows of data has various combinations of these 6 categories ; the data itself defines the valid combinations.


3. We have to assume that the user will select any particular combination starting with the top level i.e. Cat1. Now suppose Cat1 had 25 different possibilities ; the first combobox should display these 25 possibilities , and allow the user to select any one.


4. Next , the user would move to the second combobox ; the second category Cat2 could have another 25 different unique possibilites , but given the selection that has already been made of Cat1 , the number of Cat2 possibilities might be just 7. For instance , the first selection in vehicles might be Buses , Cars , Two-wheelers ; the second selection might be the tyre sizes ; under normal circumstances , the different possibilities in tyre sizes might be 15 ranging from A through O ; however , once the first selection has been done of Two-wheeler , then the number of tyre sizes narrow down to probably 4 , i.e A through D.


5. At every selection of one item out of the various possibilities , the choices available for the next combobox would have to be restricted to those which would generate a valid combination available within the data.


One way is to put a filter each time a selection is made from a combobox ; the list of choices available for the next combobox would have to be generated from the choices available in the filtered data.


Given the number of comboboxes and the amount of data , I think a formula based approach would do exactly what the OP is complaining of viz. crash Excel. VBA would be better.


Narayan
 
Hi,


Can you please download the below file and check whether you looking for this kind of report.


http://www.2shared.com/file/eLLKlDqs/5_online.html


Thanks,

Suresh Kumar s
 
It's exactly what Narayank is saying. I've checked your file Suresh and it's something like that, only that each option you select will load a large list on the following box (instead of 1 option).


Will this technique be able to deal with the large data set? I'm using combo boxes because I need to be able to change font size and color. If VBA is required, would you be able to show an example of how it would apply? I'm pretty basic when it comes to writing code.


Thanks!
 
I've just noticed by recording that VBA does the sorting and removing duplicates really fast, but how could I make it to create a list depending on a criteria?
 
Hi ,


I can write the code provided I have some sample data before me ; is it possible for you to upload your file ?


Narayan
 
Just created this example: https://docs.google.com/open?id=0B9nov_b3A5SvOE5LVmZXb0JsSzg


keep in mind there's less data (about 3K rows)


Thanks a lot
 
Hi ,


Sorry about the delay. Can you check the file here ? I have done only the populating of the 6 comboboxes ; I have not gone beyond that.


https://docs.google.com/open?id=0B0KMpuzr3MTVVWY4cjlheXpfYms


Please check out the operation of the 6 comboboxes with your full data , and confirm that there is no problem. Thereafter we can proceed with any other requirement that you may have.


Note that I have defined 7 named ranges ; you will have to define these in your workbook. Also , there are procedures in a separate code module , the ThisWorkBook section , and in the Sheet2 section. You will have to copy these to the appropriate places in your workbook.


Narayan
 
I just opened it and I think it's PERFECT. I truly appreciate your help. I'll let you know if I have any doubts. Thanks again
 
Hi Narayank, been over the file and I have 2 questions:


1) On the "City" combobox, it always includes NY as an option, regardless the country you selected previously. E.g. Brazil --> NY.


2) What if I wanted that every combobox had the option to also select "All", meaning that for example if you chose Coca Cola --> Marketing --> All, on the "Country" combobox, you wanted to see results for all the countries together. I'm thinking that the issue would be comboboxes that follow, because they depend on this value. So they would have to ignore country, and on the next combobox, "City" show all cities that are under Marketing.


I hope that was clear. Do you see any way of doing this?
 
Hi ,


Can you check the file here ?


http://speedy.sh/m54dk/cacos-Example.xlsm


I have resolved the NY problem ; it was a mistake on my part , since the logic itself was wrong ; I think I have corrected it.


Please check it out thoroughly.


Regarding the addition of the "All" selection , I'll post a solution tomorrow.


Narayan
 
Hi Narayank, somehow I cannot download the file, would you be so kind of posting a new link? Thank you


Also, let me know if you were able to see the "All" selection that we mentioned before
 
Hi ,


Can you check here ?


https://docs.google.com/open?id=0B0KMpuzr3MTVSVV0UVBwZG15RUk


The "All" selection is a minor matter ; once you confirm that the combo boxes work correctly , we can incorporate the "All" selection.


Narayan
 
Hi ,


Thanks. I'll post the revised file incorporating the "All" selection either tonight itself or tomorrow morning.


Can you clarify - do you want the "All" selection only for the CITY combobox , or can it be for all of them ?


Narayan
 
It should be for all of them, and once you select "All" on one box, the following box should skip that criteria and show all options according the box that preceded the "All".


For example, if you chose "Marketing" on box 2, and "All" in the Countries box, on the cities it should display all cities that match the criteria "Marketing", and of course like before its also dependable of the criteria that comes before Marketing.


So, if you chose "Coca Cola" --> "Marketing" --> "All", it should display all cities matching the criterias Coca Cola and Marketing.
 
Hi ,


OK. I think it's not as easy as I thought it would be. Still , I can possibly post the revised file tomorrow morning.


The "All" selection will be available in all the comboboxes , but any selection of "All" in one combobox will only affect the list of choices in the immediately succeeding combobox.


Narayan
 
You must log in or register to reply here.
Back
Top