Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 37

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

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

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

    It may have something to do with Net Framework & mscorlib.tlb.
    Maybe it needs update or the file is somehow missing.
    Check this link:
    http://www.snb-vba.eu/VBA_Sortedlist_en.html
    then read this part:
    3. The origin of the SortedList

    So basically you need to locate 'mscorlib.tlb' then set the vbe reference to it.

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

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

    Can you run this without error?

    Code:
    Sub tryX()
    Dim dar As Object
    Set dar = CreateObject("System.Collections.ArrayList")
    End Sub
    and this:

    Code:
    Sub tryY()
    Dim dar As Object
    Set dar = CreateObject("System.Collections.Sortedlist")
    End Sub
    Last edited by Akuini; Aug 16th, 2019 at 09:43 PM.

  3. #23
    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

    This UDF seems to do the trick.

    Code:
    Function StL(r As Range)
    Dim AR() As Variant: AR = r.Value
    Dim SL As Object: Set SL = CreateObject("System.Collections.SortedList")
    Dim res As String
    
    For i = LBound(AR) To UBound(AR, 2)
        If Not SL.contains(AR(1, i)) Then
            SL.Add AR(1, i), 1
        Else
            SL.Item(AR(1, i)) = SL.Item(AR(1, i)) + 1
        End If
    Next i
    
    For j = 0 To SL.Count - 1
        res = res & SL.getbyindex(j) & "|"
    Next j
    
    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.

  4. #24
    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

    Not Sure if you have Power Query available, but this works as well.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        Blanks = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        Combo = Table.AddColumn(Blanks, "Combo", each Text.Combine(List.Transform(Table.Column(Table.Group(
    Table.Group(Table.FromList(List.Transform(List.Sort(Record.FieldValues(_)),Number.From), Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1"}, {{"Count", each Table.RowCount(_), type number}}), {"Column1"}, {{"Count.1", each List.Sum([Count]), type number}}),"Count.1"),Text.From),"|"))
    in
        Combo
























    LEGO HTML
    DEFGHIJKLMNOPQR
    1n1n2n3n4n5n6n7n8n9n10n11n12n13n14Combo
    2100010101100108|6
    3211021002011006|5|3
    4300030103100108|3|3
    5400140200210008|2|2|2
    6010200300321008|2|2|2
    7121311001000016|6|1|1
    8200400010010109|3|1|1
    9011501000100208|4|1|1
    10000612101201315|5|2|1|1
    110201101000000010|3|1
    12101020201111105|7|2
    132000010000000111|2|1
    14000060150260137|2|1|1|1|2
    15111070260301205|4|2|1|1|1
    160020000100400011|1|1|1
    170010000000021011|2|1
    18012111011110204|8|2
    19123222000201006|2|5|1
    20034033101000116|4|3|1
    21105040210100207|3|2|1|1
    22210001321011006|5|2|1
    23001110402022106|4|3|1
    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.

  5. #25
    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

    Updated M code. Had an extra Table.Group function in the first one.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        Blanks = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        Combo = Table.AddColumn(Blanks, "Custom", each Text.Combine(List.Transform(Table.Column(Table.Group(Table.FromList(List.Transform(List.Sort(Record.FieldValues(_)),Number.From),Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),"Count"),Text.From),"|"))
    in
        Combo
    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.

  6. #26
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    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
    It may have something to do with Net Framework & mscorlib.tlb.
    Maybe it needs update or the file is somehow missing.
    Check this link:
    http://www.snb-vba.eu/VBA_Sortedlist_en.html
    then read this part:
    3. The origin of the SortedList

    So basically you need to locate 'mscorlib.tlb' then set the vbe reference to it.
    Hello Akuini, I could locate In the VBEditor mscorlib.dll in the References (Tools/references...) and checked it. And saw the access coming from... "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"

    But it did not worked stop at the same line with same massage. I thought it might take effect after the restarting computer but problem continue. Strange it were working fine till last month, so I tried Restoring back to an earlier date but no results...

    Thank you for your help I will continue searching if got it work will let you know

    Kind Regards,
    Moti

  7. #27
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    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
    Can you run this without error?

    Code:
    Sub tryX()
    Dim dar As Object
    Set dar = CreateObject("System.Collections.ArrayList")
    End Sub
    and this:

    Code:
    Sub tryY()
    Dim dar As Object
    Set dar = CreateObject("System.Collections.Sortedlist")
    End Sub
    Hello Akuini, no both stop at the line below
    Code:
     Set dar = CreateObject("System.Collections.ArrayList")

    And giving an error Run- time error'-2146232576 (80131700)': Automation error


    Thank you


    Kind Regards,

    Moti


  8. #28
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    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
    This UDF seems to do the trick.

    Code:
    Function StL(r As Range)
    Dim AR() As Variant: AR = r.Value
    Dim SL As Object: Set SL = CreateObject("System.Collections.SortedList")
    Dim res As String
    
    For i = LBound(AR) To UBound(AR, 2)
        If Not SL.contains(AR(1, i)) Then
            SL.Add AR(1, i), 1
        Else
            SL.Item(AR(1, i)) = SL.Item(AR(1, i)) + 1
        End If
    Next i
    
    For j = 0 To SL.Count - 1
        res = res & SL.getbyindex(j) & "|"
    Next j
    
    StL = Left(res, Len(res) - 1)
    End Function
    Hello lrobbo314, using the function in the cells S7=StL(D7:Q7) getting error #¡VALUE! Like this

    Please can you check it?


    Thank you for your help


    Kind Regards,

    Moti


    DEFGHIJKLMNOPQRS
    5n1n2n3n4n5n6n7n8n9n10n11n12n13n14Count Smaller to Larger
    6
    710001010110010#¡VALUE!

    Sheet1



    Last edited by motilulla; Aug 17th, 2019 at 02:49 AM.

  9. #29
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,755
    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
    Not Sure if you have Power Query available, but this works as well.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
        Blanks = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        Combo = Table.AddColumn(Blanks, "Combo", each Text.Combine(List.Transform(Table.Column(Table.Group(
    Table.Group(Table.FromList(List.Transform(List.Sort(Record.FieldValues(_)),Number.From), Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1"}, {{"Count", each Table.RowCount(_), type number}}), {"Column1"}, {{"Count.1", each List.Sum([Count]), type number}}),"Count.1"),Text.From),"|"))
    in
        Combo
    Hello lrobbo314, I don't know if I got Power Query available in excel 2000, because i have never used the Power Query. and don't know where to find it.

    Thank you for your help and for new suggestions


    Kind Regards,

    Moti

  10. #30
    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

    Quote Originally Posted by motilulla View Post
    Hello lrobbo314, using the function in the cells S7=StL(D7:Q7) getting error #¡VALUE! Like this

    Please can you check it?


    Thank you for your help


    Kind Regards,

    Moti

    Yeah, I tested it again, it works and you seem to have done everything correctly. I actually posted this solution without seeing the issue you were having with dlls and the sortedlist object. I'm guessing that's the issue.

    And it looks like you need at least Excel 2013 to install Power Query, so no go on that one either.

    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.

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
  •