Is this possible?

Rikki Tikki Tavi

New Member
Joined
Nov 2, 2023
Messages
12
Office Version
  1. 365
  2. 2021
Platform
  1. MacOS
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! 😜
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
1699041754333.png

VBA 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
    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
 
Upvote 0
I absolutely can, thank you!
Sample Book2.xlsx
AB
14327
24327
34327
44327
54327
64327
74327
84327
92347
102347
112347
122347
132347
143472
153472
163472
177243
187243
191190
201190
211190
221190
231190
241190
251190
261091
271091
281091
291091
309011
319011
329011
339011
341019
351019
363112
373112
383112
393112
401213
411213
422113
432113
441123
451123
465325
475325
485532
495532
502355
515235
525325
536758
548756
555768
566578
577865
589432
592943
604932
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
AB
14327
24327
34327
44327
54327
64327
74327
84327
92347
102347
112347
122347
132347
143472
153472
163472
177243
187243
191190
201190
211190
221190
231190
241190
251190
261091
271091
281091
291091
309011
319011
329011
339011
341019
351019
363112
373112
383112
393112
401213
411213
422113
432113
441123
451123
465325
475325
485532
495532
502355
515235
525325
536758
548756
555768
566578
577865
589432
592943
604932
61
62
63
Sheet1
 
Upvote 0
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 😉
 
Upvote 0
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
VBA 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
    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
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!
 
Upvote 0
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.
VBA 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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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