# Thread: Count all values of each row from smaller to larger Thanks:  6 Post #5201072 (1)Post #5327512 (1)Post #5201095 (1)Post #5201432 (1)Post #5201045 (1) Likes:  5 Post #5201432 (2)Post #5327512 (1)Post #5201045 (1)Post #5327281 (1)

1. ## Re: Count all values of each row from smaller to larger

Originally Posted by motilulla

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. ## 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
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```

3. ## Re: Count all values of each row from smaller to larger

Sorry, forgot to delete this line:
Code:
`Set vso = CreateObject("System.Collections.Sortedlist")`
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. ## Re: Count all values of each row from smaller to larger

Originally Posted by Akuini
Sorry, forgot to delete this line:
Code:
`Set vso = CreateObject("System.Collections.Sortedlist")`
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. ## Re: Count all values of each row from smaller to larger

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

6. ## 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```
Code:
```function COMBO(pRange) {
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.

7. ## Re: Count all values of each row from smaller to larger

Originally Posted by lrobbo314
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```
Code:
```function COMBO(pRange) {
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.

Have a nice weekend

Kind Regards,

Moti