Sorting a userform combobox list into an unusual order.

[jasonb75]

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

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.

 

[Kaper]

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:

=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
	A	B	C	D	E	F	G	H	I	J
1	Heading A		HeadingC		heading1		Group 1	heading1
2	V		A		F		Group 2	F
3	E		E		T		Group 3	T	4
4	U		C		R		3	R	2
5	S		F		M		3	M	2
6	N		D		S		this will be eliminated later (UNIQUE)	F	2
7	S		B		Q		3	S	2
8	Y		G		L		3	Q	1
9	H		H		H		3	L	1
10	W		I		Y		3
11	J		J		W		3
12	R		K		J		4	H	3
13	H		L		heading2		this will be eliminated later	S	1
14	H		M		A		4	Y	1
15	T		N		B		4	W	1
16	F		O		C		4	J	1
17	T		P		D		this will be eliminated later	R	1
18	R		Q		E		this will be eliminated later	T	1
19	M		R		F		this will be eliminated later	F	1
20	T		S		G		4
21	T		T		H		4
22	Q		V		I		5	heading1
23	L		Y		J		start of group 6	A
24	F		X		K			B
25	S		Z		L			C
26	M		W		M			D
27	F		U		N			E
28					O			F
29					P			G
30					Q			H
31					R			I
32					S			J
33					T			K
34					U			L
35					V			M
36					W			N
37					X			O
38					Y			P
39					Z			Q
40								R
41								S
42								T
43								U
44								V
45								W
46								X
47								Y
48								Z
49
Sheet1

Cell Formulas
Range	Formula
E1:E39	E1	=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)))
H2	H2	=A27
H3:I9	H3	=LET(list1,UNIQUE(A17:A26),SORT(HSTACK(list1,COUNTIF(A7:A26,list1)),2,-1))
H12:I19	H12	=LET(list2,UNIQUE(A7:A16),SORT(HSTACK(list2,COUNTIF(A7:A16,list2)),2,-1))
H23:H48	H23	=SORT(C2:C27)
Dynamic array formulas.

 

[Kaper]

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):

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

 

[jasonb75]

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.

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