Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 37

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

  1. #11
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    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 Fluff View Post
    NPV is available in 2003, but don't know about 2000
    However IFERROR is not available in 2000
    Thank you Fluff, for clearing about NPV that is why formula is not working does there could be a VBA solution for this trouble?

    Kind Regards,

    Moti

  2. #12
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    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 XOR LX View Post
    My mistake. Of course it's the IFERROR (and potentially also NPV, though I thought that was available in 2000), which is causing the error.



    Ah, in that case unfortunately this will not be possible using worksheet formulas alone with your version of Excel. You'll require some VBA. Are you ok with that?

    Regards
    XOR LX, yes Please if it is possible I would like to have VBA solution
    Last edited by motilulla; Jan 3rd, 2019 at 09:46 AM.

  3. #13
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    Ah, wait. I'm mistaken again. It's not the entries within those columns which is a restriction on a formula solution, but the number of columns being queried.

    I see that you have only 14 columns, so we can just do it.

    Try this array formula** and get back to me. Otherwise we'll try the VBA!

    =SUBSTITUTE(TEXT(SUM(10^(ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))-1)*COUNTIF(D7:Q7,LARGE(IF(FREQUENCY(D7:Q7,D7:Q7),TRANSPOSE(D7:Q7)),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))))),REPT("# | ",COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))," | ","",COUNT(1/FREQUENCY(D7:Q7,D7:Q7)))

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  4. #14
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    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 XOR LX View Post
    Ah, wait. I'm mistaken again. It's not the entries within those columns which is a restriction on a formula solution, but the number of columns being queried.

    I see that you have only 14 columns, so we can just do it.

    Try this array formula** and get back to me. Otherwise we'll try the VBA!

    =SUBSTITUTE(TEXT(SUM(10^(ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))-1)*COUNTIF(D7:Q7,LARGE(IF(FREQUENCY(D7:Q7,D7:Q7),TRANSPOSE(D7:Q7)),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))))),REPT("# | ",COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))," | ","",COUNT(1/FREQUENCY(D7:Q7,D7:Q7)))

    Regards
    XOR LX, I cannot use this formula because "COUNTIF" also does not support Excel 2000. I think what you thought it would be great if you can build a VBA solution Please.

    Thank you


    Kind Regards,

    Moti


  5. #15
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by motilulla View Post
    XOR LX, I cannot use this formula because "COUNTIF" also does not support Excel 2000. I think what you thought it would be great if you can build a VBA solution Please.

    Thank you


    Kind Regards,

    Moti

    Ok, just realised it was flawed in any case as it wouldn't work if a given value occurred more than 9 times within the row.

    Will get back to you soon with some VBA.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  6. #16
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    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 XOR LX View Post
    Ok, just realised it was flawed in any case as it wouldn't work if a given value occurred more than 9 times within the row.

    Will get back to you soon with some VBA.

    Regards
    Thank you XOR LX
    Last edited by motilulla; Jan 3rd, 2019 at 10:14 AM.

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

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

    Hi, motilulla
    Try this:

    Code:
    Sub a1082382a()
    'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.htmlDim vso As Object
    Dim va, vb
    Dim i As Long, j As Long, k As Long, s As Long
    Dim d As Object, vso As Object, x As Variant
    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
    
        For Each x In d
            vso.Add x, d.Item(x)
        Next
        
        For i = 0 To vso.count - 1
            vb(j, 1) = vb(j, 1) & "|" & vso.GetByIndex(i)
        Next
        
        vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1)
        
        
    Next
    
    Range("S7").Resize(UBound(vb, 1), 1) = vb
    End Sub

  8. #18
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    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
    Hi, motilulla
    Try this:

    Code:
    Sub a1082382a()
    'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.htmlDim vso As Object
    Dim va, vb
    Dim i As Long, j As Long, k As Long, s As Long
    Dim d As Object, vso As Object, x As Variant
    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
    
        For Each x In d
            vso.Add x, d.Item(x)
        Next
        
        For i = 0 To vso.count - 1
            vb(j, 1) = vb(j, 1) & "|" & vso.GetByIndex(i)
        Next
        
        vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1)
        
        
    Next
    
    Range("S7").Resize(UBound(vb, 1), 1) = vb
    End Sub
    Akuini, your VBA code work very perfect! Thank you very much for the help!

    Query solved!

    Kind Regards,

    Moti

  9. #19
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 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
    Akuini, your VBA code work very perfect! Thank you very much for the help!

    Query solved!

    Kind Regards,

    Moti
    Your welcome & thanks for the reply

  10. #20
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,751
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hello Akuini, I am wondering this VBA were working perfect till last month, tried today it stop at the line below
    Code:
     Set vso = CreateObject("System.Collections.Sortedlist")
    and giving an error Run- time error'-2146232576 (80131700)': Automation error I goggled to find the solution but do not get the answer

    Please need help do you have any idea what is wrong? I am running excel 2000


    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
  •