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]

Welcome to the MrExcel board!

I have read through that a few times and I am not able to grasp what you want.
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]

Welcome to the MrExcel board!

I have read through that a few times and I am not able to grasp what you want.
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?

I absolutely can, thank you!

 

[Proulxs]

Short answer it is possible.

Long answer:
This would require a few formulas and since my excel is in french I dare not give you directly each formula as it might not work and would require tweaking.
But here the big lines of how I would do it;
From the main set of numbers add 4 columns that would break down the 4 digit number into its component;
Formula here would be =value(left(A1;1)) (replace A1 with location of your data)
For the other 3 digit I would do =value(right(left(A1;2);1)) (to take the first 2 digits then take only the rightmost digit of the pair. Repeat for the other 2 digits by bumping number in left parameter by 1 each.
N.B. I use value to make sure the numbers extracted are kept into number rather than text to make it easier to work with after.

In the next 4 columns I would add a formula to sort the 4 numbers in order. Formula would be simple =small(B1:E1;1) through =small(B1:E1;4) where B1:E1 is where I had the first 4 formulas done to extract digits. =Small will give the Nth smallest value.

Then I would recombine the 4 digits with a simple =F1*1000+G1*100+H1*10+I1

Then I would in another sheet put a =small(J:J;line()) -> This would check the whole line of J or where you have your recombining formula and the parameter line() would mean the formula would look at the Nth value that match the number of the line... So on line 3 it would look at 3rd value. If you put a header on your data you could simply do line()-1 for example.
And finally add a =countif of your new data that would count only up to the line you are at to show how many time you have seen that combination thus far. so =countif(A$1:A1;A1) then pull that formula down. As you progress in lines you'll see A$1:A2;A2 and so forth. It will show increasing number each time you encounter a number you already saw. You can then add a filter to hide all the non-1 value.

You now have a list of all combinations used without repeating re-mix of the same numbers. If you add a =count(A:A;A1) you can also know how many times this combination or remix of it has been seen.

If you want from there obtain a list in order of frequency I would replace the =count(A:A;A1) with a =count(A:A;A1)+line()/10000 so every number is unique. You would not have 9 twice you would have 9.00001 and 9.00007. This would allow you to, on another sheet, use a =small on the new count to display as before the Nth value where 9.00007 would show before 9.00001. Then you can use an Xlookup to find which Combination 9.00007 match too.

... Yes it's possible, but require a bit of playing around in excel. Have fun.

 

[Legacy 143009]

I came up with a clumsy VBA solution. With the setup below, my code sorts column A numbers to column B. I wrote the reasons in G,H,I columns. I hope I understood correctly.
I am also attaching the sample file for you to play with.

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
    End If
    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) = ""
        End If
      End If
      k = c
    Next
  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

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

go.wetransfer.com

 

[Rikki Tikki Tavi]

I absolutely can, thank you!

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
62
63
Sheet1

Short answer it is possible.

Long answer:
This would require a few formulas and since my excel is in french I dare not give you directly each formula as it might not work and would require tweaking.
But here the big lines of how I would do it;
From the main set of numbers add 4 columns that would break down the 4 digit number into its component;
Formula here would be =value(left(A1;1)) (replace A1 with location of your data)
For the other 3 digit I would do =value(right(left(A1;2);1)) (to take the first 2 digits then take only the rightmost digit of the pair. Repeat for the other 2 digits by bumping number in left parameter by 1 each.
N.B. I use value to make sure the numbers extracted are kept into number rather than text to make it easier to work with after.

In the next 4 columns I would add a formula to sort the 4 numbers in order. Formula would be simple =small(B1:E1;1) through =small(B1:E1;4) where B1:E1 is where I had the first 4 formulas done to extract digits. =Small will give the Nth smallest value.

Then I would recombine the 4 digits with a simple =F1*1000+G1*100+H1*10+I1

Then I would in another sheet put a =small(J:J;line()) -> This would check the whole line of J or where you have your recombining formula and the parameter line() would mean the formula would look at the Nth value that match the number of the line... So on line 3 it would look at 3rd value. If you put a header on your data you could simply do line()-1 for example.
And finally add a =countif of your new data that would count only up to the line you are at to show how many time you have seen that combination thus far. so =countif(A$1:A1;A1) then pull that formula down. As you progress in lines you'll see A$1:A2;A2 and so forth. It will show increasing number each time you encounter a number you already saw. You can then add a filter to hide all the non-1 value.

You now have a list of all combinations used without repeating re-mix of the same numbers. If you add a =count(A:A;A1) you can also know how many times this combination or remix of it has been seen.

If you want from there obtain a list in order of frequency I would replace the =count(A:A;A1) with a =count(A:A;A1)+line()/10000 so every number is unique. You would not have 9 twice you would have 9.00001 and 9.00007. This would allow you to, on another sheet, use a =small on the new count to display as before the Nth value where 9.00007 would show before 9.00001. Then you can use an Xlookup to find which Combination 9.00007 match too.

... Yes it's possible, but require a bit of playing around in excel. Have fun.

Thank you for your reply! Very kind of you to take the time to explain this to me, it's a bit complicated, since I'm a newbie at excel, but I bet if I read this 3,000 times I'll start to get it. I'm kidding, seriously thank you very much! I am going to attach a mini sheet just to make sure I was clear what the goal is. However, from the example it does look like you understood my long winded explanation.

I absolutely can, thank you!

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
62
63
Sheet1

 

[Rikki Tikki Tavi]

Welcome to the MrExcel board!

I have read through that a few times and I am not able to grasp what you want.
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?

I was able to get that mini sheet posted, a few times, uh oh.. anyway. You should see it

 

[Rikki Tikki Tavi]

Short answer it is possible.

Long answer:
This would require a few formulas and since my excel is in french I dare not give you directly each formula as it might not work and would require tweaking.
But here the big lines of how I would do it;
From the main set of numbers add 4 columns that would break down the 4 digit number into its component;
Formula here would be =value(left(A1;1)) (replace A1 with location of your data)
For the other 3 digit I would do =value(right(left(A1;2);1)) (to take the first 2 digits then take only the rightmost digit of the pair. Repeat for the other 2 digits by bumping number in left parameter by 1 each.
N.B. I use value to make sure the numbers extracted are kept into number rather than text to make it easier to work with after.

In the next 4 columns I would add a formula to sort the 4 numbers in order. Formula would be simple =small(B1:E1;1) through =small(B1:E1;4) where B1:E1 is where I had the first 4 formulas done to extract digits. =Small will give the Nth smallest value.

Then I would recombine the 4 digits with a simple =F1*1000+G1*100+H1*10+I1

Then I would in another sheet put a =small(J:J;line()) -> This would check the whole line of J or where you have your recombining formula and the parameter line() would mean the formula would look at the Nth value that match the number of the line... So on line 3 it would look at 3rd value. If you put a header on your data you could simply do line()-1 for example.
And finally add a =countif of your new data that would count only up to the line you are at to show how many time you have seen that combination thus far. so =countif(A$1:A1;A1) then pull that formula down. As you progress in lines you'll see A$1:A2;A2 and so forth. It will show increasing number each time you encounter a number you already saw. You can then add a filter to hide all the non-1 value.

You now have a list of all combinations used without repeating re-mix of the same numbers. If you add a =count(A:A;A1) you can also know how many times this combination or remix of it has been seen.

If you want from there obtain a list in order of frequency I would replace the =count(A:A;A1) with a =count(A:A;A1)+line()/10000 so every number is unique. You would not have 9 twice you would have 9.00001 and 9.00007. This would allow you to, on another sheet, use a =small on the new count to display as before the Nth value where 9.00007 would show before 9.00001. Then you can use an Xlookup to find which Combination 9.00007 match too.

... Yes it's possible, but require a bit of playing around in excel. Have fun.

I came up with a clumsy VBA solution. With the setup below, my code sorts column A numbers to column B. I wrote the reasons in G,H,I columns. I hope I understood correctly.
I am also attaching the sample file for you to play with.
View attachment 101438

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
    End If
    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) = ""
        End If
      End If
      k = c
    Next
  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

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

go.wetransfer.com

So kind of you to get this to me, thank you very much! I really thought excel was the numbers gateway to the gods. I'd just enter some data push a button and schwing, it would be done. Everyone is so kind to give me the harsh reality that nah, it takes some real work, time, and brain sweat. But I am determined and intrigued!

 

[Legacy 143009]

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

 

[Rikki Tikki Tavi]

Welcome to the MrExcel board!

I have read through that a few times and I am not able to grasp what you want.
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?

Welcome to the MrExcel board!

I have read through that a few times and I am not able to grasp what you want.
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?

Hi Peter, I did get some wonderful advice, and marked the replies as solved. Truthfully, I don't know what I don't know about excel, so I am not sure if my it's as solved as it can be! So please, if you have anything to offer don't hesitate, I put up that mini sheet. Thanks again for your help and advice!