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!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Legacy 143009]

Hi,

I konow the thread is already answered. But I wanted to perfect my code. Here is the VBA solution from me. Maybe someone seeking for a VBA code for a similar problem may benefit from this. Best regards!

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

  i = 1
  ReDim tempArr(1 To 2, 1 To i)
  'First transfer unique values to an array to play with
  For Each temp In numbers
    If Not numbersDic.Exists(temp) Then
      numbersDic.Add temp, 1
      tempArr(1, i) = temp
      tempArr(2, i) = countIfArray(temp, numbers)
      i = i + 1
      ReDim Preserve tempArr(1 To 2, 1 To i)
    End If
  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, 1) = tempArr(1, i)
      Next
      k = c
      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, 1) = tempArr(1, j)
            Next
            tempArr(1, j) = ""
          End If
        End If
        k = c
      Next
    End If
  Next
  Range("B2").Resize(UBound(numbers)).Value = 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

 

[Peter_SSs]

I konow the thread is already answered. But I wanted to perfect my code. Here is the VBA solution from me. Maybe someone seeking for a VBA code for a similar problem may benefit from this. Best regards!

That does not quite work for me. For the sample data given in post #19 (which is the sample data from post #16 but in random order) but starting in A2, your code produced this for me in the last few rows.
That is, a group of 3 followed by a group of 5, which is the wrong way around.
(It also still would not work for this OP due to them using a Mac system)

Rikki Tikki Tavi.xlsm
	B
54	4932
55	9432
56	2943
57	6758
58	5768
59	7865
60	8756
61	6578
Sheet2 (2)

 

[Legacy 143009]

Ok @Peter_SSs
Got the question wrong then. Sorry.
I worked with your sample and had similar results. I must be missing something.

 

[Shloime]

Hi,
I tried to go through the formula I think I got it , many formulas i was not aware of,
but it seems it is being sorted only by frequency, how do you make sure to have in order if you have 2 numbers or 2 number combination that are the same frequent?

 

[Peter_SSs]

it seems it is being sorted only by frequency, how do you make sure to have in order if you have 2 numbers or 2 number combination that are the same frequent?

I'm not certain what you are asking but I think it is this.
My formula does three sorts.
Using the data/results in post #19

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

First, the first/main sort is by frequency (or count) of the group of 4 digits.
So the yellow section all have the digits "2347" and there are 18 of those which is most, blue section all have the digits "0119" and there are 17 of those which is second most, green "1123" has 10, third most etc

Second, within those groups the second sort is a sort by the count of the original numbers.
So within that first yellow section "4327" occurs 8 times, "2347" five times, "3472" three times and "7243" two times, and hence the groups are sorted in decreasing size within that yellow section.

Finally, if any groups in a section after that second sort are equal in size the third sort is by the individual number itself.
For example, in the bright green section "2113", "1213" and '1123" all occur two times. I have sorted those numbers in decreasing size.

I hope that is what you wanted explained.

 

[Shloime]

if you'd have 4327" "2347" , "3472" "7243" all 2 times how would you still have the 2 "4327" together?

 

[Peter_SSs]

if you'd have 4327" "2347" , "3472" "7243" all 2 times how would you still have the 2 "4327" together?

Because of the third sort

Finally, if any groups in a section after that second sort are equal in size the third sort is by the individual number itself.

Those values you described all occurred twice in your example so those numbers get sorted by the number itself. So both of the 7243 values would get sorted to the top, then the 4327 values etc.
Small example

Rikki Tikki Tavi.xlsm
	A	B	C
1	4327		7243
2	1111		7243
3	2347		4327
4	4327		4327
5	7243		3472
6	3472		3472
7	2222		2347
8	2347		2347
9	3333		4444
10	4444		3333
11	3472		2222
12	7243		1111
Shloime sample

Cell Formulas
Range	Formula
C1:C12	C1	=LET(a,A1:A12,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.

 

[Shloime]

Hi

Because of the third sort

Those values you described all occurred twice in your example so those numbers get sorted by the number itself. So both of the 7243 values would get sorted to the top, then the 4327 values etc.
Small example

Rikki Tikki Tavi.xlsm
	A	B	C
1	4327		7243
2	1111		7243
3	2347		4327
4	4327		4327
5	7243		3472
6	3472		3472
7	2222		2347
8	2347		2347
9	3333		4444
10	4444		3333
11	3472		2222
12	7243		1111
Shloime sample

Cell Formulas
Range	Formula
C1:C12	C1	=LET(a,A1:A12,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.

look at this example:

4327 9685
1111 9685
2347 8569
4327 8569
7243 7243
3472 7243
2222 6958
2347 6958
3333 5968
4444 5968
3472 4327
7243 4327
6958 3472
1111 3472
5968 2347
6958 2347
8569 1111
9685 1111
5555 9999
5968 6666
9999 5555
6666 4444
9685 3333
8569 2222

 

[Peter_SSs]

look at this example:

Ah, yes, now I see what you are getting at. If two (or more) of the major groups have the same number of entries it creates a problem.

Is this better?

Rikki Tikki Tavi.xlsm
	A	B
1	4327	9685
2	1111	9685
3	2347	8569
4	4327	8569
5	7243	6958
6	3472	6958
7	2222	5968
8	2347	5968
9	3333	7243
10	4444	7243
11	3472	4327
12	7243	4327
13	6958	3472
14	1111	3472
15	5968	2347
16	6958	2347
17	8569	1111
18	9685	1111
19	5555	9999
20	5968	6666
21	9999	5555
22	6666	4444
23	9685	3333
24	8569	2222
Shloime sample (2)

Cell Formulas
Range	Formula
B1:B24	B1	=LET(a,A1:A24,s,SEQUENCE(4),x,--BYROW(a,LAMBDA(rw,CONCAT(SORT(MID(rw,s,1))))),m,BYROW(x,LAMBDA(y,SUM(MID(y,s,1)*10^SEQUENCE(4,,4,-1)))),SORTBY(a,INDEX(FREQUENCY(x,SEQUENCE(MAX(x))),x)*10^6+m,-1,COUNTIF(a,a),-1,a,-1))
Dynamic array formulas.