Page 4 of 4 FirstFirst ... 234
Results 31 to 37 of 37

Thread: Count all values of each row from smaller to larger

  1. #31
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,064
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by motilulla View Post

    Thank you for your help I will continue searching if got it work will let you know
    Moti
    Let's try another way without using the sortedlist object:

    Code:
    Sub a1082382b()
    'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.html
    Dim va, vb
    Dim i As Long, j As Long, k As Long, s As Long, z As Long
    Dim d As Object
    va = Range("D7", Cells(Rows.count, "Q").End(xlUp))
    ReDim vb(1 To UBound(va, 1), 1 To 1)
    
    For j = 1 To UBound(va, 1)
        Set vso = CreateObject("System.Collections.Sortedlist")
        Set d = CreateObject("scripting.dictionary")
        
        For k = 1 To UBound(va, 2)
            s = va(j, k)
            If Not d.Exists(s) Then
                d(s) = 1
                Else
                d(s) = d(s) + 1
            End If
        Next
        arr = d.Keys
    
        For i = 0 To UBound(arr)
            z = WorksheetFunction.Small(arr, i + 1)
    '        Debug.Print k, d(z)
             vb(j, 1) = vb(j, 1) & "|" & d(z)
        Next i
        
        vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1)
    Next
    
    Range("S7").Resize(UBound(vb, 1), 1) = vb
    End Sub

  2. #32
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Same idea, slightly different approach. Hopefully you don't have the same problems with scripting dictionaries as you do sortedlists.

    Code:
    Function StL(r As Range)
    Dim AR() As Variant: AR = r.Value
    Dim SD As Object: Set SD = CreateObject("Scripting.Dictionary")
    Dim res As String
    Dim tmp As Integer
    Dim TA As Variant
    
    For i = LBound(AR) To UBound(AR, 2)
        If Not SD.Exists(AR(1, i)) Then
            SD.Add AR(1, i), 1
        Else
            SD.Item(AR(1, i)) = SD.Item(AR(1, i)) + 1
        End If
    Next i
    
    TA = SD.keys
    
    For i = 0 To UBound(TA)
        For j = i To UBound(TA)
            If TA(i) > TA(j) Then
                tmp = TA(i)
                TA(i) = TA(j)
                TA(j) = tmp
            End If
        Next j
    Next
    
    For k = 0 To UBound(TA)
        res = res & SD.Item(TA(k)) & "|"
    Next k
    
    StL = Left(res, Len(res) - 1)
    
    End Function
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #33
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,064
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Sorry, forgot to delete this line:
    Code:
    Set vso = CreateObject("System.Collections.Sortedlist")
    use this one instead:
    Code:
    Sub a1082382c()
    'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.html
    Dim va, vb
    Dim i As Long, j As Long, k As Long, s As Long, z As Long
    Dim d As Object
    va = Range("D7", Cells(Rows.count, "Q").End(xlUp))
    ReDim vb(1 To UBound(va, 1), 1 To 1)
    
    For j = 1 To UBound(va, 1)
        Set d = CreateObject("scripting.dictionary")
        
        For k = 1 To UBound(va, 2)
            s = va(j, k)
            If Not d.Exists(s) Then
                d(s) = 1
                Else
                d(s) = d(s) + 1
            End If
        Next
        arr = d.Keys
    
        For i = 0 To UBound(arr)
            z = WorksheetFunction.Small(arr, i + 1)
    '        Debug.Print k, d(z)
             vb(j, 1) = vb(j, 1) & "|" & d(z)
        Next i
        
        vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1)
    Next
    
    Range("S7").Resize(UBound(vb, 1), 1) = vb
    End Sub

  4. #34
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,765
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by Akuini View Post
    Sorry, forgot to delete this line:
    Code:
    Set vso = CreateObject("System.Collections.Sortedlist")
    use this one instead:
    Code:
    Sub a1082382c()
    'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.html
    Dim va, vb
    Dim i As Long, j As Long, k As Long, s As Long, z As Long
    Dim d As Object
    va = Range("D7", Cells(Rows.count, "Q").End(xlUp))
    ReDim vb(1 To UBound(va, 1), 1 To 1)
    
    For j = 1 To UBound(va, 1)
        Set d = CreateObject("scripting.dictionary")
        
        For k = 1 To UBound(va, 2)
            s = va(j, k)
            If Not d.Exists(s) Then
                d(s) = 1
                Else
                d(s) = d(s) + 1
            End If
        Next
        arr = d.Keys
    
        For i = 0 To UBound(arr)
            z = WorksheetFunction.Small(arr, i + 1)
    '        Debug.Print k, d(z)
             vb(j, 1) = vb(j, 1) & "|" & d(z)
        Next i
        
        vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1)
    Next
    
    Range("S7").Resize(UBound(vb, 1), 1) = vb
    End Sub
    Outstanding! Akuini, much kind of you it worked like magic

    I appreciate your help a lot for solving it multiple times

    Have a good weekend


    Kind Regards,

    Moti


  5. #35
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,064
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    You're welcome, glad to help, & thanks for the feedback.

  6. #36
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Just for kicks, especially keeping older versions of Excel in mind, here are 2 more options. The first is a purely array based UDF, and the second, which I might consider if I was stuck with Excel 2000, is a way to do this using Google Sheets instead of Excel. Unfortunately, passing ranges into custom functions is a bit of a mess in Sheets, so I will also show how you have to write the formula.

    Excel (Only Array, no Scripting Dictionary or SortedList)
    Code:
    Function JA(r As Range) As String
    Dim AR As Variant: AR = r.Value
    Dim res As String: res = ""
    Dim cnt As Long: cnt = 1
    Dim tmp As Integer
    
    
    For i = LBound(AR) To UBound(AR, 2)
        For j = i To UBound(AR, 2)
            If AR(1, i) > AR(1, j) Then
                tmp = AR(1, i)
                AR(1, i) = AR(1, j)
                AR(1, j) = tmp
            End If
        Next j
    Next i
    
    
    For k = LBound(AR) + 1 To UBound(AR, 2)
        If AR(1, k) = AR(1, k - 1) Then
            cnt = cnt + 1
        Else
            res = res & cnt & "|"
            cnt = 1
        End If
    Next k
    
    
    JA = res & cnt
    End Function
    Google Sheets
    Code:
    function COMBO(pRange) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var arr = sheet.getRange(pRange).getValues();
      
      arr = transposeArray(arr);
      arr.sort();
    
    
      return getCounts(arr);
    }
    
    
    function transposeArray(array){
      var result = [];
      for (var col = 0; col < array[0].length; col++) { // Loop over array cols
        result[col] = [];
        for (var row = 0; row < array.length; row++) { // Loop over array rows
          result[col][row] = array[row][col]; // Rotate
        }
      }
      return result;
    }
    
    
    function getCounts(array) {
      var res = "";
      var cnt = 1;
      for (var i =1; i < array.length; i++) {
        Logger.log(i + ": " + array[i] + ", " + array[i-1]);
        if (array[i]+0 == array[i-1]+0) {
          cnt ++;
        } else {
          res = res + cnt + "|";
          cnt = 1;
        }
      }
      res+=cnt;
      return res;
    }
    And the formula to copy down, =COMBO(ADDRESS(ROW(A2),COLUMN(A2),4)&":"&ADDRESS(ROW(N2),COLUMN(N2),4)). Where A2 is the first cell in the row and N2 is the last cell in the row.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  7. #37
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,765
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count all values of each row from smaller to larger

    Quote Originally Posted by lrobbo314 View Post
    Just for kicks, especially keeping older versions of Excel in mind, here are 2 more options. The first is a purely array based UDF, and the second, which I might consider if I was stuck with Excel 2000, is a way to do this using Google Sheets instead of Excel. Unfortunately, passing ranges into custom functions is a bit of a mess in Sheets, so I will also show how you have to write the formula.

    Excel (Only Array, no Scripting Dictionary or SortedList)
    Code:
    Function JA(r As Range) As String
    Dim AR As Variant: AR = r.Value
    Dim res As String: res = ""
    Dim cnt As Long: cnt = 1
    Dim tmp As Integer
    
    
    For i = LBound(AR) To UBound(AR, 2)
        For j = i To UBound(AR, 2)
            If AR(1, i) > AR(1, j) Then
                tmp = AR(1, i)
                AR(1, i) = AR(1, j)
                AR(1, j) = tmp
            End If
        Next j
    Next i
    
    
    For k = LBound(AR) + 1 To UBound(AR, 2)
        If AR(1, k) = AR(1, k - 1) Then
            cnt = cnt + 1
        Else
            res = res & cnt & "|"
            cnt = 1
        End If
    Next k
    
    
    JA = res & cnt
    End Function
    Google Sheets
    Code:
    function COMBO(pRange) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var arr = sheet.getRange(pRange).getValues();
      
      arr = transposeArray(arr);
      arr.sort();
    
    
      return getCounts(arr);
    }
    
    
    function transposeArray(array){
      var result = [];
      for (var col = 0; col < array[0].length; col++) { // Loop over array cols
        result[col] = [];
        for (var row = 0; row < array.length; row++) { // Loop over array rows
          result[col][row] = array[row][col]; // Rotate
        }
      }
      return result;
    }
    
    
    function getCounts(array) {
      var res = "";
      var cnt = 1;
      for (var i =1; i < array.length; i++) {
        Logger.log(i + ": " + array[i] + ", " + array[i-1]);
        if (array[i]+0 == array[i-1]+0) {
          cnt ++;
        } else {
          res = res + cnt + "|";
          cnt = 1;
        }
      }
      res+=cnt;
      return res;
    }
    And the formula to copy down, =COMBO(ADDRESS(ROW(A2),COLUMN(A2),4)&":"&ADDRESS(ROW(N2),COLUMN(N2),4)). Where A2 is the first cell in the row and N2 is the last cell in the row.
    Hello lrobbo314, the "Function JA" results fine. I did not new it about "Google Sheets" every day there are new options thank you for letting me recognize.

    I do appreciate your help


    Have a nice weekend


    Kind Regards,

    Moti

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •