Count all values of each row from smaller to larger

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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? </SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN> </SPAN>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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:

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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. </SPAN></SPAN>

Thank you
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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!</SPAN></SPAN>

Query solved!

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>:)
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,602
Messages
5,487,806
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top