Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old December 9th 14, 10:38 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default Formula (or code) return the largest number of duplicates in a list.

Hi Howard,

Am Tue, 9 Dec 2014 00:52:09 -0800 (PST) schrieb L. Howard:

F1 is the query value
F2 is the formula value of the max consecutive occurrences.

I still get an error when F1 is = to the Value in F2.
If F1 is one less than F2 then it runs okay. But gives a false return.


there is a problem with the table layout. For the first entry j has to
be 1 and the result has to be in cells(i,2). For all other entries j has
to be 0 and the result has to be in cells(i-1,2)
It is easier to do it with a formula. Look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for your workbook and activate the sheet "Formula".
There you get a result changing F1 without running a macro.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

  #12   Report Post  
Old December 9th 14, 11:29 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 852
Default Formula (or code) return the largest number of duplicates in a list.


There you get a result changing F1 without running a macro.


Very nice!!

Thanks for the help. Never occurred to me that could be done with formulas.

Regards,
Howard
  #13   Report Post  
Old December 9th 14, 12:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default Formula (or code) return the largest number of duplicates in a list.

Hi Howard,

Am Tue, 9 Dec 2014 02:29:27 -0800 (PST) schrieb L. Howard:

Never occurred to me that could be done with formulas.


if you want do it with a macro you have to go another way.
Try:

Sub Duplicates()
Dim LRow As Long, i As Long, Start As Long, n As Long, k As Long
Dim varOut() As Variant, varTemp As Variant

With Sheets("Sheet1")
.Range("B:C").ClearContents
k = .Range("F1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Start = 1
Do
If WorksheetFunction.CountIf(.Range(.Cells(Start, 1), _
.Cells(Start + k - 1, 1)), .Cells(Start, 1)) = k Then
.Cells(Start + k - 1, 2) = .Cells(Start, 1) & _
" = " & k & " items"
Start = Start + k
Else
Start = Start + 1
End If
Loop While Start <= LRow

LRow = .Cells(Rows.Count, 2).End(xlUp).Row
varTemp = .Range("B1:B" & LRow)
ReDim Preserve varOut(WorksheetFunction.CountA(.Range("B1:B" & LRow))
- 1, 0)
For i = LBound(varTemp) To UBound(varTemp)
If Len(varTemp(i, 1)) 0 Then
varOut(n, 0) = varTemp(i, 1)
n = n + 1
End If
Next
.Range("C1").Resize(UBound(varOut) + 1) = varOut
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #14   Report Post  
Old December 9th 14, 04:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 852
Default Formula (or code) return the largest number of duplicates in a list.


That is great having both formula and VBA.

On my computer both run in about 16 to 20 seconds, and I assume with 13000+ rows that is quite reasonable given the calc's it is doing.

The first 9 rows on the formula sheet column B have no formulas, I assume that has to do with the use of OFFSET in the formula. Just guessing on my part, but why would it be nine rows?

Thanks for the nice work.

Howard
  #15   Report Post  
Old December 9th 14, 04:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default Formula (or code) return the largest number of duplicates in a list.

Hi Howard,

Am Tue, 9 Dec 2014 07:19:25 -0800 (PST) schrieb L. Howard:

On my computer both run in about 16 to 20 seconds, and I assume with 13000+ rows that is quite reasonable given the calc's it is doing.


the code is faster if you delete the formula column

The first 9 rows on the formula sheet column B have no formulas, I assume that has to do with the use of OFFSET in the formula. Just guessing on my part, but why would it be nine rows?


If OFFSET becomes negative you get an error. Cells F1 value + 1 rows
must be without formula. If you want each second entry you can drag the
formula to B3. If you want each 10th entry code in Worksheet_Change
deletes the formula from row 1 to 11 to avoid the error.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #16   Report Post  
Old December 9th 14, 04:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default Formula (or code) return the largest number of duplicates in a list.

Hi again,

Am Tue, 9 Dec 2014 16:26:31 +0100 schrieb Claus Busch:

the code is faster if you delete the formula column


if you write Appliation.ScreenUpdating = false
the code will also a little bit faster.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #17   Report Post  
Old December 9th 14, 07:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 852
Default Formula (or code) return the largest number of duplicates in a list.

On Tuesday, December 9, 2014 7:28:37 AM UTC-8, Claus Busch wrote:
Hi again,

Am Tue, 9 Dec 2014 16:26:31 +0100 schrieb Claus Busch:

the code is faster if you delete the formula column


if you write Appliation.ScreenUpdating = false
the code will also a little bit faster.


I did both of those suggestions and the time is about half.

Good stuff and many thanks.

Howard


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I select the largest number in a list... Dr. Darrell Excel Discussion (Misc queries) 1 January 21st 09 07:30 PM
Search multiple sheets and return largest number found mpenkala Excel Worksheet Functions 2 January 4th 07 05:41 PM
return largest number of a list of numbers in the same cell Hells Excel Worksheet Functions 2 October 5th 05 01:52 PM
Finding the 3rd largest number in a list Simon Jefford Excel Worksheet Functions 2 June 28th 05 04:01 PM
finding the second largest number in a list bobf Excel Discussion (Misc queries) 1 February 16th 05 02:19 PM


All times are GMT +1. The time now is 01:38 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017