Is this possible?

[Rikki Tikki Tavi]

HI there,

I have a 2020 mac with Microsoft 365 Excel, but I am newbie on ye olde Excel. I have spent hours on the Tube of You trying to figure this out. Not sure if anyone wants to take the time to indulge me but thought give it try! You can write this charity off on your taxes right?

How would I sort a column of cells that contain four numbers each. I want to sort the column not by the numeric value of the numbers in each cell, but by the numbers in the cell as a group, and by the frequency they occur in the column.
For example 2347, 1191, 2280, and then 2347 happens again BUT the set of numbers are NOT IN THAT ORDER, instead it's 4327. Lets say that out of 900 numbers 4327 appears 7 times, and that combination of numbers occurs a total of 24 times in different order.
However, the combination 4327 is repeated the most,7 x’s so it is sorted at the top of the column, 1-7.
Then the rest of the sets of 2347 are listed in order, by the number of times they appear, in places 8-24.
Then comes the second most repeated combination of numbers. Etc.
Can excel sort the column of numbers in this manner by the frequency those four numbers occur in every order they appear, and then move on to the second most frequent number and so on and so on?
Can I enter the data into each cell as four numbers or do I have to split the numbers up?

Any help would be greatly appreciated and I'll gladly email you a receipt for your trouble!

 

[Rikki Tikki Tavi]

No problem. I hope worked for you. However I am still unhappy with the some part of my code. Try like this. If it messes up, roll back to previous code.

Sub test()
  Dim numbersDic As Object
  Dim numbers As Variant, i As Long, j As Long, k As Integer, c As Long
  Dim temp As Variant, tempArr As Variant
 
  With Application
  numbers = .Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))

  'First put unique numbers with their quantities in a dictionary
  'It will be useful to sort according to their appearance
  Set numbersDic = CreateObject("Scripting.Dictionary")
  For Each temp In numbers
    If Not numbersDic.Exists(temp) Then
      numbersDic.Add temp, countIfArray(temp, numbers)
    End If
  Next
  ReDim tempArr(1 To 2, 1 To 1)
  i = 1
 
  'Now transfer unique values to an array to play with
  For Each temp In numbersDic
    tempArr(1, i) = temp
    tempArr(2, i) = numbersDic(temp)
    i = i + 1
    ReDim Preserve tempArr(1 To 2, 1 To i)
  Next
  ReDim Preserve tempArr(1 To 2, 1 To i - 1)
 
  'Now sort in descending order according to their appearance
  For i = 1 To UBound(tempArr, 2) - 1
    For j = i + 1 To UBound(tempArr, 2)
      If tempArr(2, j) >= tempArr(2, i) Then
        For k = 1 To 2
          temp = tempArr(k, i)
          tempArr(k, i) = tempArr(k, j)
          tempArr(k, j) = temp
        Next
      End If
    Next
  Next

  'Now find out which are the combinations
  k = 1
  c = 1
  For i = 1 To UBound(tempArr, 2)
    If tempArr(1, i) <> "" Then
      For c = k To (k + tempArr(2, i)) - 1
        numbers(c) = tempArr(1, i)
      Next
      k = c - 1
      For j = i + 1 To UBound(tempArr, 2)
        If tempArr(1, j) <> "" Then
          If isCombination(tempArr(1, j), tempArr(1, i)) Then
            For c = k To (k + tempArr(2, j)) - 1
              numbers(c) = tempArr(1, j)
            Next
            tempArr(1, j) = ""
            k = c
          End If
        End If
      Next
    End If
  Next
  Range("B2").Resize(UBound(numbers)).Value = .Transpose(numbers)
  End With
End Sub
Function isCombination(ByVal tmp1 As Variant, ByVal tmp2 As Variant) As Boolean
  Dim temp3 As Variant, i As Integer, j As Integer
  tmp1 = Split(StrConv(tmp1, vbUnicode), Chr$(0))
  ReDim Preserve tmp1(UBound(tmp1) - 1)
  tmp2 = Split(StrConv(tmp2, vbUnicode), Chr$(0))
  ReDim Preserve tmp2(UBound(tmp2) - 1)
  tmp3 = tmp1
  For i = 0 To UBound(tmp1)
    For j = 0 To UBound(tmp2)
      If tmp1(i) = tmp2(j) Then
        tmp1(i) = 0
      End If
    Next
  Next
  For i = 0 To UBound(tmp2)
    For j = 0 To UBound(tmp3)
      If tmp2(i) = tmp3(j) Then
        tmp2(i) = 0
      End If
    Next
  Next
  If Evaluate(Join(tmp1, "+")) + Evaluate(Join(tmp2, "+")) = 0 Then
    isCombination = True
  End If
End Function
Function countIfArray(ByVal temp As Variant, ParamArray numbers() As Variant) As Integer
  Dim number As Variant
  For Each number In numbers(0)
    If number = temp Then countIfArray = countIfArray + 1
  Next
End Function

Okay, I'll give it a go - thank you very much for tweaking it!

 

[Rikki Tikki Tavi]

Okay, I'll give it a go - thank you very much for tweaking it!

Really Very very very kind of you! I just copied this to the clipboard, now I'm going to try and get this into my computer. It may take me awhile to actually get this up and running in excel, probably a couple of days, but as soon as I do I will report back to you, thank you again for you hard work!

 

[Peter_SSs]

I did get some wonderful advice, and marked the replies as solved.

now I'm going to try and get this into my computer.

The second quote here seems to indicate that you don't yet know if the suggestion in the marked solution is in fact a solution for your problem. If you don't yet know, please remove the marked solution for the moment at least.

Further, you stated that you are using Excel on a Mac, as your profile shows too. If that is the case, my understanding is that none of @Flashbond's suggestions will work on your machine, though I don't have a Mac to check for sure.

I put up that mini sheet.

You did, thanks. However my request asked for three things. The mini sheet appears to show the blue part but not the red (or is it the red part and not the blue?). The purple part is also missing.

Could you make up a small sample of, say, 20 to 30 rows and post that and the expected results done manually with XL2BB and explain again in relation to the sample data?

 

[Rikki Tikki Tavi]

The second quote here seems to indicate that you don't yet know if the suggestion in the marked solution is in fact a solution for your problem. If you don't yet know, please remove the marked solution for the moment at least.

Further, you stated that you are using Excel on a Mac, as your profile shows too. If that is the case, my understanding is that none of @Flashbond's suggestions will work on your machine, though I don't have a Mac to check for sure.


You did, thanks. However my request asked for three things. The mini sheet appears to show the blue part but not the red (or is it the red part and not the blue?). The purple part is also missing.

Well I see, back to the drawing board. I will remove the check mark and try to provide you with rest of the items you asked for. Perhaps I am over my head here...

 

[Rikki Tikki Tavi]

No problem. I hope worked for you. However I am still unhappy with the some part of my code. Try like this. If it messes up, roll back to previous code.

Sub test()
  Dim numbersDic As Object
  Dim numbers As Variant, i As Long, j As Long, k As Integer, c As Long
  Dim temp As Variant, tempArr As Variant
 
  With Application
  numbers = .Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))

  'First put unique numbers with their quantities in a dictionary
  'It will be useful to sort according to their appearance
  Set numbersDic = CreateObject("Scripting.Dictionary")
  For Each temp In numbers
    If Not numbersDic.Exists(temp) Then
      numbersDic.Add temp, countIfArray(temp, numbers)
    End If
  Next
  ReDim tempArr(1 To 2, 1 To 1)
  i = 1
 
  'Now transfer unique values to an array to play with
  For Each temp In numbersDic
    tempArr(1, i) = temp
    tempArr(2, i) = numbersDic(temp)
    i = i + 1
    ReDim Preserve tempArr(1 To 2, 1 To i)
  Next
  ReDim Preserve tempArr(1 To 2, 1 To i - 1)
 
  'Now sort in descending order according to their appearance
  For i = 1 To UBound(tempArr, 2) - 1
    For j = i + 1 To UBound(tempArr, 2)
      If tempArr(2, j) >= tempArr(2, i) Then
        For k = 1 To 2
          temp = tempArr(k, i)
          tempArr(k, i) = tempArr(k, j)
          tempArr(k, j) = temp
        Next
      End If
    Next
  Next

  'Now find out which are the combinations
  k = 1
  c = 1
  For i = 1 To UBound(tempArr, 2)
    If tempArr(1, i) <> "" Then
      For c = k To (k + tempArr(2, i)) - 1
        numbers(c) = tempArr(1, i)
      Next
      k = c - 1
      For j = i + 1 To UBound(tempArr, 2)
        If tempArr(1, j) <> "" Then
          If isCombination(tempArr(1, j), tempArr(1, i)) Then
            For c = k To (k + tempArr(2, j)) - 1
              numbers(c) = tempArr(1, j)
            Next
            tempArr(1, j) = ""
            k = c
          End If
        End If
      Next
    End If
  Next
  Range("B2").Resize(UBound(numbers)).Value = .Transpose(numbers)
  End With
End Sub
Function isCombination(ByVal tmp1 As Variant, ByVal tmp2 As Variant) As Boolean
  Dim temp3 As Variant, i As Integer, j As Integer
  tmp1 = Split(StrConv(tmp1, vbUnicode), Chr$(0))
  ReDim Preserve tmp1(UBound(tmp1) - 1)
  tmp2 = Split(StrConv(tmp2, vbUnicode), Chr$(0))
  ReDim Preserve tmp2(UBound(tmp2) - 1)
  tmp3 = tmp1
  For i = 0 To UBound(tmp1)
    For j = 0 To UBound(tmp2)
      If tmp1(i) = tmp2(j) Then
        tmp1(i) = 0
      End If
    Next
  Next
  For i = 0 To UBound(tmp2)
    For j = 0 To UBound(tmp3)
      If tmp2(i) = tmp3(j) Then
        tmp2(i) = 0
      End If
    Next
  Next
  If Evaluate(Join(tmp1, "+")) + Evaluate(Join(tmp2, "+")) = 0 Then
    isCombination = True
  End If
End Function
Function countIfArray(ByVal temp As Variant, ParamArray numbers() As Variant) As Integer
  Dim number As Variant
  For Each number In numbers(0)
    If number = temp Then countIfArray = countIfArray + 1
  Next
End Function

I did check those boxes on my profile that said I was on a mac but for some reason it didn't get posted with my avatar, very sorry for that! Thank you so much for your work on my behalf...

 

[Rikki Tikki Tavi]

I absolutely can, thank you!

Well I see, back to the drawing board. I will remove the check mark and try to provide you with rest of the items you asked for. Perhaps I am over my head here...

This is not the raw data.
It is in the final form that I would like it in.
The raw data looks exactly the same, but not in any order. The raw data consists of around 1500 cells of four numbers, but the order is completely random. I want to organize the numbers in the following manner as illustrated below:

1. By overall frequency occurring in the column. So 4327 occurs 18 times, it's occurs most frequently of the total 1500 numbers in the column.
2. I organize that set by frequency of the order they occur. 4327 occurs 8 times, so it's the most frequent, its at the top, then each set is organized in descending order
*If all that can be done is that the same four numbers can be grouped together that would be fine, brilliant really.
So 4327, 3427, 7342 - if those could end up all together in the column GREAT! I can organize them from there myself!

Sample Book2.xlsx
	A	B
1	4327
2	4327
3	4327
4	4327
5	4327
6	4327
7	4327
8	4327
9	2347
10	2347
11	2347
12	2347
13	2347
14	3472
15	3472
16	3472
17	7243
18	7243
19	1190
20	1190
21	1190
22	1190
23	1190
24	1190
25	1190
26	1091
27	1091
28	1091
29	1091
30	9011
31	9011
32	9011
33	9011
34	1019
35	1019
36	3112
37	3112
38	3112
39	3112
40	1213
41	1213
42	2113
43	2113
44	1123
45	1123
46	5325
47	5325
48	5532
49	5532
50	2355
51	5235
52	5325
53	6758
54	8756
55	5768
56	6578
57	7865
58	9432
59	2943
60	4932
61
Sheet1

 

[Dave Patton]

With your 60 rows of data, what are your expected results?
With the stated numbers, the first few are

T202311a.xlsm
	D	E
2	4327	8
3	1190	7
4	2347	5
1d

Cell Formulas
Range	Formula
D2:D25	D2	=SORTBY(B2#,C2:C25,-1)
E2:E4	E2	=COUNTIFS(rN,D2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rN	='1d'!$A$2:$A$61	E2:E4

 

[Dave Patton]

Post #17 Please ignore the formulas part.

I was trying to determine the results that you require.

	Sort results by largest count
4327	8		4327	8
1190	7		1190	7
2347	5		2347	5
1091	4		9011	4
9011	4		3112	4
3112	4		1091	4
3472	3		5325	3
5325	3		3472	3

 

[Peter_SSs]

I want to organize the numbers in the following manner as illustrated below:

1. By overall frequency occurring in the column. So 4327 occurs 18 times, it's occurs most frequently of the total 1500 numbers in the column.
2. I organize that set by frequency of the order they occur. 4327 occurs 8 times, so it's the most frequent, its at the top, then each set is organized in descending order

Thanks for the further clarification. See if this does what you want.
Just for visual checking I have manually coloured the major groupings in column C and the sub-groups in column D.

Rikki Tikki Tavi.xlsm
	A	B	C	D
1	1213		4327
2	3472		4327
3	5325		4327
4	2943		4327
5	1190		4327
6	2347		4327
7	1190		4327
8	4327		4327
9	1190		2347
10	1190		2347
11	6578		2347
12	1123		2347
13	2347		2347
14	4327		3472
15	7243		3472
16	5235		3472
17	1091		7243
18	1091		7243
19	9011		1190
20	4327		1190
21	3472		1190
22	1213		1190
23	4327		1190
24	3112		1190
25	8756		1190
26	1091		9011
27	1123		9011
28	5325		9011
29	2347		9011
30	4327		1091
31	1190		1091
32	3112		1091
33	1019		1091
34	7865		1019
35	5532		1019
36	9011		3112
37	7243		3112
38	5768		3112
39	5532		3112
40	2347		2113
41	9011		2113
42	1091		1213
43	2347		1213
44	1019		1123
45	1190		1123
46	4327		5325
47	3472		5325
48	3112		5325
49	2113		5532
50	9432		5532
51	3112		5235
52	1190		2355
53	6758		8756
54	4932		7865
55	4327		6758
56	5325		6578
57	9011		5768
58	4327		9432
59	2113		4932
60	2355		2943
Sheet3

Cell Formulas
Range	Formula
C1:C60	C1	=LET(a,A1:A60,x,--BYROW(a,LAMBDA(rw,CONCAT(SORT(MID(rw,SEQUENCE(4),1))))),SORTBY(a,INDEX(FREQUENCY(x,SEQUENCE(MAX(x))),x),-1,COUNTIF(a,a),-1,a,-1))
Dynamic array formulas.

 

[Rikki Tikki Tavi]

That's exactly what I needed! Thank you so much, it's perfect!!