Sorting a userform combobox list into an unusual order.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,520
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I think that the person I'm doing this for is coming up with an impossible idea, I've been scratching my head over this for a couple of days and have nothing more than fingers full of splinters :oops:

I have a feeling that this may be one of those things that if it is possible, is likely to also be impractical. At the moment I'm just looking at ideas and opinions rather than trying to get a full blown solution, unless of course there is somebody out there that can see something I'm missing that makes this incredibly simple.

I don't currently have a sample sheet to work with at present,

We're starting with 2 columns of data. Ultimately they will be on separate sheets, but for now I'm using columns A and C in the same sheet.
Column C is a list of unique names, think data validation source.
Column A will contain selections made from that list, so some might appear once, some multiple times, and others not at all.

The seemingly impossible task that has been thrown at me, is to sort the unique list to populate a combobox from these lists in the following order (please assume data extends down to A100 for this example).

Steps 3 and 4 show fixed ranges in the example but would ideally be x and y number of rows starting from the bottom. In the event that there are less rows than set by the variable then simply use all available rows, skipping step 4 if all rows are used by step 3.
  1. Heading (most likely from a string variable)
  2. Name in A100
  3. Unique Names from A90:A99 (excluding Name from A100 if repeated) sorted by count of records in A80:A99 (just to confirm, that is not a typo).
  4. Unique Names from A80:A89 (excluding Names from previous 2 steps if repeated) sorted by count of records in A80:A89.
  5. Another heading from a string variable
  6. Entire list from column C in alphabetical order (including anything previously listed in steps 2-4).

I think that covers everything.

Thank you in advance for any suggestions that you may have.
 
Probably the best tool will be VBA, but also with formulas (as you use Excel 365) you can do this.
To have a more compact screenshot I limited rows to 27 (heading of columns A and C and data A-Z) and the content of produced list is:
1. Heading (most likely from a string variable)
2. Name in A27
3. Unique Names from A17:A26 (excluding Name from A27 if repeated) sorted by count of records in A7:A26 (just to confirm, that is not a typo). Note that A27 could be repeted at first moment, it will be removed at later stage
4. Unique Names from A7:A16 (excluding Names from previous 2 steps if repeated) sorted by count of records in A7:A16. Again names from previous steps could be included at the moment
5. Another heading from a string variable (I added both this heading and a heading from point 1 after completing list from pts 2-4, just in case any of these headings is between data in column C - probably it would never happen, so the formula could be a bit shorter),
6. Entire list from column C in alphabetical order (including anything previously listed in steps 2-4).

In columns G:I are presented the results from each of steps alone. Just for seeing hof final formula (in E1) works.

The formula in E1:
Excel Formula:
=LET(list1,UNIQUE(A17:A26),part1,SORT(HSTACK(list1,COUNTIF(A7:A26,list1)),2,-1),list2,UNIQUE(A7:A16),VSTACK("heading1",UNIQUE(TAKE(VSTACK(A27,part1,SORT(HSTACK(list2,COUNTIF(A7:A16,list2)),2,-1)),,1)),"heading2",SORT(C2:C27)))

So you can remove columns G:I. Moreover, you probably can also use a formula from E1 as a name in a Name manager (Ctrl+F3)

Enjoy :-)

Book1
ABCDEFGHIJ
1Heading AHeadingCheading1Group 1heading1
2VAFGroup 2F
3EETGroup 3T4
4UCR3R2
5SFM3M2
6NDSthis will be eliminated later (UNIQUE)F2
7SBQ3S2
8YGL3Q1
9HHH3L1
10WIY3
11JJW3
12RKJ4H3
13HLheading2this will be eliminated laterS1
14HMA4Y1
15TNB4W1
16FOC4J1
17TPDthis will be eliminated laterR1
18RQEthis will be eliminated laterT1
19MRFthis will be eliminated laterF1
20TSG4
21TTH4
22QVI5heading1
23LYJstart of group 6A
24FXKB
25SZLC
26MWMD
27FUNE
28OF
29PG
30QH
31RI
32SJ
33TK
34UL
35VM
36WN
37XO
38YP
39ZQ
40R
41S
42T
43U
44V
45W
46X
47Y
48Z
49
Sheet1
Cell Formulas
RangeFormula
E1:E39E1=LET(list1,UNIQUE(A17:A26),part1,SORT(HSTACK(list1,COUNTIF(A7:A26,list1)),2,-1),list2,UNIQUE(A7:A16),VSTACK("heading1",UNIQUE(TAKE(VSTACK(A27,part1,SORT(HSTACK(list2,COUNTIF(A7:A16,list2)),2,-1)),,1)),"heading2",SORT(C2:C27)))
H2H2=A27
H3:I9H3=LET(list1,UNIQUE(A17:A26),SORT(HSTACK(list1,COUNTIF(A7:A26,list1)),2,-1))
H12:I19H12=LET(list2,UNIQUE(A7:A16),SORT(HSTACK(list2,COUNTIF(A7:A16,list2)),2,-1))
H23:H48H23=SORT(C2:C27)
Dynamic array formulas.
 
Last edited:
Upvote 0
So back to the title - to fill in a comboin in Userform VBA will be probably best. And the above formula based solution can be treated just as a description of algorithm.
Or ... you could use also Evaluate to use formula to create an array you could later use to fill your combo box. see for the above sheet (just cols A and C filled):

VBA Code:
Sub test()
Dim var As Variant
var = Evaluate("=LET(list1,UNIQUE(A17:A26),part1,SORT(HSTACK(list1,COUNTIF(A7:A26,list1)),2,-1),list2,UNIQUE(A7:A16),VSTACK(""heading1"",UNIQUE(TAKE(VSTACK(A27,part1,SORT(HSTACK(list2,COUNTIF(A7:A16,list2)),2,-1)),,1)),""heading2"",SORT(C2:C27)))")
End Sub
Set breakpoint on End sub and examine in locals window var variable which contains 39x1 array of elements
 
Upvote 0
Thank you very much for this, @Kaper, I think you've nailed it 😲

(I added both this heading and a heading from point 1 after completing list from pts 2-4, just in case any of these headings is between data in column C - probably it would never happen, so the formula could be a bit shorter)
The headings shouldn't find their way into the data in column C but it could be possible from user error.
The list that is populating the combobox is for consistent spelling, etc with existing records (data validation in a userform), the user can still add a new entry in the form if they don't find anything that matches what they are looking for. In this case, the new entry will be added to the list in column C so that it appears in the combobox list next time.

With that in mind, it could be possible that the heading is added to the list in column C if the user selects it by mistake and submits the form without realising this. :unsure:

This definitely gives me something to work with, I think that I might have to use a hidden sheet (or several) to process the lists by formula then pull them to the combo form there. Reason being is that I will be using this for 3 or 4 different comboboxes / lists, where the list for the second box needs to be filtered based on the selection in the first, etc.

I've had something come up that is putting this project on hold for a while, I do think that I will end up using your suggestion and marking it as the solution but I'm going to leave the thread open for a few days to see if any alternative theories come up.

Thank you very much again for your help

Jason
 
Upvote 0

Forum statistics

Threads
1,226,840
Messages
6,193,278
Members
453,788
Latest member
drcharle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top