Count all values of each row from smaller to larger

motilulla · Jan 2, 2019

Hello,

I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible?

As per example below

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

3

4

5

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Count Smaller to Larger

6

7

1

0

1

0

1

0

1

0

1

0

6 | 8

8

2

1

0

2

1

0

2

0

1

0

6 | 5 | 3

9

3

0

3

0

1

0

3

1

0

1

0

8 | 3 | 3

10

4

0

1

4

0

2

0

2

1

0

8 | 2 | 2 | 2

11

0

1

0

2

0

3

0

3

2

1

0

8 | 2 | 2 | 2

12

1

2

1

3

1

0

1

0

1

6 | 6 | 1 | 1

13

2

0

4

0

1

0

1

0

1

0

9 | 3 | 1

14

0

1

5

0

1

0

1

0

2

0

8 | 4 | 1 | 1

15

0

6

1

2

1

0

1

2

0

1

3

1

5 | 5 | 2 | 1 | 1

16

0

2

0

1

0

1

0

10 | 3 | 1

17

1

0

1

0

2

0

2

0

1

0

5 | 7 | 2

18

2

0

1

0

1

11 | 2

19

0

6

0

1

5

0

2

6

0

1

3

7 | 2 | 1 | 1 | 1 | 2

20

1

0

7

0

2

6

0

3

0

1

2

0

5 | 4 | 2 | 1 | 1 | 1

21

0

2

0

1

0

4

0

11 | 1 | 1 | 1

22

0

1

0

2

1

0

11 | 2 | 1

23

0

1

2

1

0

1

0

2

0

4 | 8 | 2 |

24

1

2

3

2

0

2

0

1

0

6 | 2 | 5 | 1

25

0

3

4

0

3

1

0

1

0

1

6 | 4 | 3 | 1

26

1

0

5

0

4

0

2

1

0

1

0

2

0

7 | 3 | 2 | 1 | 1

27

2

1

0

1

3

2

1

0

1

0

6 | 5 | 2 | 1

28

0

1

0

4

0

2

0

2

1

0

6 | 4 | 3 | 1

Sheet1

Thank you all

Excel 2000
Regards,
Moti

Akuini · Aug 16, 2019

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.

Akuini · Aug 16, 2019

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

lrobbo314 · Aug 16, 2019

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

lrobbo314 · Aug 17, 2019

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

<table valign="top"><caption>LEGO HTML</caption><col width="54"><col width="124"><col width="124"><col width="124"><col width="104"><col width="104"><col width="104"><col width="104"><col width="104"><col width="104"><col width="104"><col width="104"><col width="104"><col width="110"><col width="110"><col width="110">
<tr><td></td><th style="background-color:rgb(166, 166, 166)">D</th><th style="background-color:rgb(166, 166, 166)">E</th><th style="background-color:rgb(166, 166, 166)">F</th><th style="background-color:rgb(166, 166, 166)">G</th><th style="background-color:rgb(166, 166, 166)">H</th><th style="background-color:rgb(166, 166, 166)">I</th><th style="background-color:rgb(166, 166, 166)">J</th><th style="background-color:rgb(166, 166, 166)">K</th><th style="background-color:rgb(166, 166, 166)">L</th><th style="background-color:rgb(166, 166, 166)">M</th><th style="background-color:rgb(166, 166, 166)">N</th><th style="background-color:rgb(166, 166, 166)">O</th><th style="background-color:rgb(166, 166, 166)">P</th><th style="background-color:rgb(166, 166, 166)">Q</th><th style="background-color:rgb(166, 166, 166)">R</th></tr>
<tr><th style="background-color:rgb(166, 166, 166)">1</th><td style="background-color:rgb(112, 173, 71)">n1</td><td style="background-color:rgb(112, 173, 71)">n2</td><td style="background-color:rgb(112, 173, 71)">n3</td><td style="background-color:rgb(112, 173, 71)">n4</td><td style="background-color:rgb(112, 173, 71)">n5</td><td style="background-color:rgb(112, 173, 71)">n6</td><td style="background-color:rgb(112, 173, 71)">n7</td><td style="background-color:rgb(112, 173, 71)">n8</td><td style="background-color:rgb(112, 173, 71)">n9</td><td style="background-color:rgb(112, 173, 71)">n10</td><td style="background-color:rgb(112, 173, 71)">n11</td><td style="background-color:rgb(112, 173, 71)">n12</td><td style="background-color:rgb(112, 173, 71)">n13</td><td style="background-color:rgb(112, 173, 71)">n14</td><td style="background-color:rgb(112, 173, 71)">Combo</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">2</th><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">8|6</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">3</th><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">6|5|3</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">4</th><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">8|3|3</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">5</th><td style="background-color:rgb(255, 255, 255)">4</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">4</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">8|2|2|2</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">6</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">8|2|2|2</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">7</th><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">3</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">6|6|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">8</th><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">4</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">9|3|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">9</th><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">5</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">8|4|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">10</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">6</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">5|5|2|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">11</th><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">10|3|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">12</th><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">5|7|2</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">13</th><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">11|2|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">14</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">6</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">5</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">6</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">7|2|1|1|1|2</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">15</th><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">7</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">6</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">3</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">5|4|2|1|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">16</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">4</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">11|1|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">17</th><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">11|2|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">18</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">4|8|2</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">19</th><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">3</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">6|2|5|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">20</th><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">4</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">6|4|3|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">21</th><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">5</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">4</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">7|3|2|1|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">22</th><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">3</td><td style="background-color:rgb(226, 239, 218)">2</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">1</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">0</td><td style="background-color:rgb(226, 239, 218)">6|5|2|1</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">23</th><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">4</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">2</td><td style="background-color:rgb(255, 255, 255)">1</td><td style="background-color:rgb(255, 255, 255)">0</td><td style="background-color:rgb(255, 255, 255)">6|4|3|1</td></tr></table>

lrobbo314 · Aug 17, 2019

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

Code:

[SIZE=1]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[/SIZE]

motilulla · Aug 17, 2019

Akuini said:
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

motilulla · Aug 17, 2019

Akuini said:
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

motilulla · Aug 17, 2019

lrobbo314 said:

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

Book1

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

5

n1

n2

n3

n4

n5

n6

n7

n8

n9

n10

n11

n12

n13

n14

Count Smaller to Larger

6

7

1

0

1

0

1

0

1

0

1

0

#VALUE!

Sheet1

motilulla · Aug 17, 2019

lrobbo314 said:

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

lrobbo314 · Aug 17, 2019

motilulla said:
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.

Count all values of each row from smaller to larger

motilulla

Well-known Member

Akuini

Well-known Member

Excel Facts

Akuini

Well-known Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

lrobbo314

Well-known Member

Similar threads

Forum statistics

Share this page

Count all values of each row from smaller to larger

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock