Good morning everyone,

On an Excel Spreadsheet from D10 to D.... (more than a
thousand, I have different transit numbers written.

With the MODE Function I know which one recurs the most...
How can I find the four following ones?

There's a VBA function I use, but it still only gives me
the transit that comes back the most often... Here's the
code.....

Public Function AMM(Plg As Range) As Variant
Dim V As Variant, res As Variant, C As Variant
Dim P As String, i As Long

P = Plg.Address
V = Evaluate("=IF(COUNTIF(" & P & "," & P & _
")=MAX(IF(" & P & "<"""",COUNTIF(" & P & _
"," & P & ")))," & P & ","""")")

ReDim res(1 To 1)

i = 0
For Each C In V
If IsNumeric(C) Then
If IsError(Application.Match(C, res, 0)) Then
' enregistre seulement la première occurence
i = i + 1
ReDim Preserve res(1 To i)
res(i) = C
End If
End If
Next C
AMM = Application.Transpose(res) ' list vertically
End Function
'Daniel Maher

Could anyone help me finding the following four transits
in the range D10: D....

Thanks for your time...

Denys

Just for fun (and a boring lunch) I wrote a function to return a to
value using only code. It will return one value sort of like the SMAL
and LARGE functions do. Call it like:

=ModePlus(D10:D2000,1) - Same as Mode
=ModePlus(D10:D2000,2) - Second Rank
etc.


Code
-------------------
Public Function ModePlus(rIn As Range, iNum As Integer)

Dim r As Range
Dim sItem() As String
Dim iItem() As Long
Dim iMax As Long
Dim i As Long
Dim SortArray() As String
Dim iCount As Integer
Dim strTemp As String
Dim iComma As Long
Dim retVal As String

iMax = rIn.Rows.Count
ReDim sItem(rIn.Rows.Count)
ReDim iItem(rIn.Rows.Count)

For Each r In rIn

For i = 1 To iMax
If sItem(i) = r.Value Then
iItem(i) = iItem(i) + 1
Exit For
End If
If sItem(i) = "" Then
sItem(i) = r.Value
iItem(i) = 1
Exit For
End If

Next

Next

For i = 1 To iMax
If sItem(i) = "" Then Exit For
Next
ReDim Preserve sItem(i - 1)
ReDim Preserve iItem(i - 1)
ReDim SortArray(i - 1)


For i = 1 To UBound(sItem)
If SortArray(iItem(i)) = "" Then
SortArray(iItem(i)) = sItem(i)
Else
SortArray(iItem(i)) = SortArray(iItem(i)) & "," & sItem(i)
End If
Next

iCount = 1
For i = UBound(SortArray) To 1 Step -1
If SortArray(i) < "" Then
strTemp = strTemp & "," & SortArray(i)
End If
Next

iComma = 0
For i = 1 To iNum
iComma = InStr(iComma + 1, strTemp, ",")
Debug.Print "FINDING COMMA: " & iComma
Next

retVal = Mid(strTemp, iComma + 1, InStr(iComma + 1, strTemp, ",") - iComma - 1)
ModePlus = retVal

End Functio
-------------------



--
Message posted from http://www.ExcelForum.com

Hi K,

Thanks a lot..... However this is returning a #value as
answer. I wonder why.... I do only have number from D10 to
D1000.....

I inserted the Function in a module... I can retrieve it,
but doesn't want to give me an answer...

Any idea....

Thanks a lot for your time

Denys


-----Original Message-----
Just for fun (and a boring lunch) I wrote a function to
return a top
value using only code. It will return one value sort of
like the SMALL
and LARGE functions do. Call it like:

=ModePlus(D10:D2000,1) - Same as Mode
=ModePlus(D10:D2000,2) - Second Rank
etc.


Code:
--------------------
Public Function ModePlus(rIn As Range, iNum As Integer)

Dim r As Range
Dim sItem() As String
Dim iItem() As Long
Dim iMax As Long
Dim i As Long
Dim SortArray() As String
Dim iCount As Integer
Dim strTemp As String
Dim iComma As Long
Dim retVal As String

iMax = rIn.Rows.Count
ReDim sItem(rIn.Rows.Count)
ReDim iItem(rIn.Rows.Count)

For Each r In rIn

For i = 1 To iMax
If sItem(i) = r.Value Then
iItem(i) = iItem(i) + 1
Exit For
End If
If sItem(i) = "" Then
sItem(i) = r.Value
iItem(i) = 1
Exit For
End If

Next

Next

For i = 1 To iMax
If sItem(i) = "" Then Exit For
Next
ReDim Preserve sItem(i - 1)
ReDim Preserve iItem(i - 1)
ReDim SortArray(i - 1)


For i = 1 To UBound(sItem)
If SortArray(iItem(i)) = "" Then
SortArray(iItem(i)) = sItem(i)
Else
SortArray(iItem(i)) = SortArray(iItem(i)) & "," & sItem
(i)
End If
Next

iCount = 1
For i = UBound(SortArray) To 1 Step -1
If SortArray(i) < "" Then
strTemp = strTemp & "," & SortArray(i)
End If
Next

iComma = 0
For i = 1 To iNum
iComma = InStr(iComma + 1, strTemp, ",")
Debug.Print "FINDING COMMA: " & iComma
Next

retVal = Mid(strTemp, iComma + 1, InStr(iComma + 1,
strTemp, ",") - iComma - 1)
ModePlus = retVal

End Function
--------------------

K


---
Message posted from http://www.ExcelForum.com/

.

Here's how I tested it:

I entered =Int(Rand()*100) into cell B10. Then I copied it down t
around cell B1500. I did a copy, paste special to save the number
without formulae.

Then in Cell B9 I entered =ModePlus(B10:B1500,1)

The value retrieved was the same as using the mode function. Can yo
give this a test and see if it works?

Here's another question. Do you have commas in your data? If so, w
will need to make a bit of a change to make it work right.



--
Message posted from http://www.ExcelForum.com

Hi K,

Me again,

The function works fine on a range of about 100 cells...
Anything I should know as why it returns #value from
D10:D1000 ???

Maybe something I am doing wrong ??

Thanks

Denys
-----Original Message-----
Just for fun (and a boring lunch) I wrote a function to
return a top
value using only code. It will return one value sort of
like the SMALL
and LARGE functions do. Call it like:

=ModePlus(D10:D2000,1) - Same as Mode
=ModePlus(D10:D2000,2) - Second Rank
etc.


Code:
--------------------
Public Function ModePlus(rIn As Range, iNum As Integer)

Dim r As Range
Dim sItem() As String
Dim iItem() As Long
Dim iMax As Long
Dim i As Long
Dim SortArray() As String
Dim iCount As Integer
Dim strTemp As String
Dim iComma As Long
Dim retVal As String

iMax = rIn.Rows.Count
ReDim sItem(rIn.Rows.Count)
ReDim iItem(rIn.Rows.Count)

For Each r In rIn

For i = 1 To iMax
If sItem(i) = r.Value Then
iItem(i) = iItem(i) + 1
Exit For
End If
If sItem(i) = "" Then
sItem(i) = r.Value
iItem(i) = 1
Exit For
End If

Next

Next

For i = 1 To iMax
If sItem(i) = "" Then Exit For
Next
ReDim Preserve sItem(i - 1)
ReDim Preserve iItem(i - 1)
ReDim SortArray(i - 1)


For i = 1 To UBound(sItem)
If SortArray(iItem(i)) = "" Then
SortArray(iItem(i)) = sItem(i)
Else
SortArray(iItem(i)) = SortArray(iItem(i)) & "," & sItem
(i)
End If
Next

iCount = 1
For i = UBound(SortArray) To 1 Step -1
If SortArray(i) < "" Then
strTemp = strTemp & "," & SortArray(i)
End If
Next

iComma = 0
For i = 1 To iNum
iComma = InStr(iComma + 1, strTemp, ",")
Debug.Print "FINDING COMMA: " & iComma
Next

retVal = Mid(strTemp, iComma + 1, InStr(iComma + 1,
strTemp, ",") - iComma - 1)
ModePlus = retVal

End Function
--------------------

K


---
Message posted from http://www.ExcelForum.com/

.

Not sure EXACTLY what I am doing wrong, but if you change the line

ReDim SortArray(i - 1)

to read

ReDim SortArray(10000)

I think it will work. I'll look into the code and see what my error i
sometime next week.



--
Message posted from http://www.ExcelForum.com

Hi K,

I tried what yoyu suggested, and it works perfectly
fine... Then I copied all my number in the column D and
pasted them on a new worksheet in column C.. Then in
column D, I did = C10 * 1 etc... to make sure it has a
numeric value....

I tried the function again, and gave me the #value answer..

And no... I did'nt have any comma those numbers came from
another Excel worksheet.... The problem is obviously not
your function....

I'll try again

Thanks

Denys


-----Original Message-----
Here's how I tested it:

I entered =Int(Rand()*100) into cell B10. Then I copied
it down to
around cell B1500. I did a copy, paste special to save
the numbers
without formulae.

Then in Cell B9 I entered =ModePlus(B10:B1500,1)

The value retrieved was the same as using the mode
function. Can you
give this a test and see if it works?

Here's another question. Do you have commas in your
data? If so, we
will need to make a bit of a change to make it work right.

K


---
Message posted from http://www.ExcelForum.com/

.

Hi Denys

Here is a formula solution.
Assuming Data in D10:D1200.

In e.g. H10 enter this formula
=MODE($D$10:$D$1200)

In H11 enter this array formula:
=MODE(IF(COUNTIF($H$10:H10,$D$10:$D$1200)=0,$D$10: $D$1200))

Please notice the use of mixed absolute and relative
references in $H$10:H10
This formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display
the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

Copy H11 down with the fill handle (the little square in the lower
right corner of the cell) to H14.

H10 will now return the Mode and H11:H14 the following four.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Denys" skrev i en meddelelse
...
Good morning everyone,

On an Excel Spreadsheet from D10 to D.... (more than a
thousand, I have different transit numbers written.

With the MODE Function I know which one recurs the most...
How can I find the four following ones?

There's a VBA function I use, but it still only gives me
the transit that comes back the most often... Here's the
code.....

Public Function AMM(Plg As Range) As Variant
Dim V As Variant, res As Variant, C As Variant
Dim P As String, i As Long

P = Plg.Address
V = Evaluate("=IF(COUNTIF(" & P & "," & P & _
")=MAX(IF(" & P & "<"""",COUNTIF(" & P & _
"," & P & ")))," & P & ","""")")

ReDim res(1 To 1)

i = 0
For Each C In V
If IsNumeric(C) Then
If IsError(Application.Match(C, res, 0)) Then
' enregistre seulement la première occurence
i = i + 1
ReDim Preserve res(1 To i)
res(i) = C
End If
End If
Next C
AMM = Application.Transpose(res) ' list vertically
End Function
'Daniel Maher

Could anyone help me finding the following four transits
in the range D10: D....

Thanks for your time...

Denys

Nice solution, Leo!

Regards,

Vasant.

"Leo Heuser" wrote in message
...
Hi Denys

Here is a formula solution.
Assuming Data in D10:D1200.

In e.g. H10 enter this formula
=MODE($D$10:$D$1200)

In H11 enter this array formula:
=MODE(IF(COUNTIF($H$10:H10,$D$10:$D$1200)=0,$D$10: $D$1200))

Please notice the use of mixed absolute and relative
references in $H$10:H10
This formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display
the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

Copy H11 down with the fill handle (the little square in the lower
right corner of the cell) to H14.

H10 will now return the Mode and H11:H14 the following four.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Denys" skrev i en meddelelse
...
Good morning everyone,

On an Excel Spreadsheet from D10 to D.... (more than a
thousand, I have different transit numbers written.

With the MODE Function I know which one recurs the most...
How can I find the four following ones?

There's a VBA function I use, but it still only gives me
the transit that comes back the most often... Here's the
code.....

Public Function AMM(Plg As Range) As Variant
Dim V As Variant, res As Variant, C As Variant
Dim P As String, i As Long

P = Plg.Address
V = Evaluate("=IF(COUNTIF(" & P & "," & P & _
")=MAX(IF(" & P & "<"""",COUNTIF(" & P & _
"," & P & ")))," & P & ","""")")

ReDim res(1 To 1)

i = 0
For Each C In V
If IsNumeric(C) Then
If IsError(Application.Match(C, res, 0)) Then
' enregistre seulement la première occurence
i = i + 1
ReDim Preserve res(1 To i)
res(i) = C
End If
End If
Next C
AMM = Application.Transpose(res) ' list vertically
End Function
'Daniel Maher

Could anyone help me finding the following four transits
in the range D10: D....

Thanks for your time...

Denys

Thanks, Vasant :-)

Regards
Leo

"Vasant Nanavati" <vasantn *AT* aol *DOT* com skrev i en meddelelse
...
Nice solution, Leo!

Regards,

Vasant.

Thanks a lot Leo..
Have a nice weeken

Denys

You're welcome, Denys.
You too :-)

LeoH


"Denys" skrev i en meddelelse
...
Thanks a lot Leo...
Have a nice weekend

Denys