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

motilulla · Jan 3, 2019

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

motilulla · Jan 3, 2019

XOR LX said:
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

XOR LX · Jan 3, 2019

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

motilulla · Jan 3, 2019

XOR LX said:
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

XOR LX · Jan 3, 2019

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

motilulla · Jan 3, 2019

XOR LX said:
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

Akuini · Jan 3, 2019

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

motilulla · Jan 3, 2019

Akuini said:

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

Akuini · Jan 4, 2019

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

motilulla · Aug 16, 2019

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

Count all values of each row from smaller to larger

motilulla

Well-known Member

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

XOR LX

Well-known Member

motilulla

Well-known Member

XOR LX

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

motilulla

Well-known Member

Akuini

Well-known Member

motilulla

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