Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurences and rows they are on
Hi, I have a range ("B1:U1000") I would like to count how many
times the values between 14060 and 14100 appear and how many rows the values appear on... i.e 14068 occurs 6 times in the range, on 4 different rows 14099 occurs 8 times in the range, on 3 different rows The range and values will be variable. I am going to incorporate this into some other code so a macro is best for me. Thankyou very much to anyone that can help. ste |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurences and rows they are on
"ste mac" skrev i en meddelelse
oups.com... Hi, I have a range ("B1:U1000") I would like to count how many times the values between 14060 and 14100 appear and how many rows the values appear on... i.e 14068 occurs 6 times in the range, on 4 different rows 14099 occurs 8 times in the range, on 3 different rows The range and values will be variable. I am going to incorporate this into some other code so a macro is best for me. Thankyou very much to anyone that can help. ste Hi ste Here's one way. CountFrequency(14070) = 12 CountRows(14070) = 5 means that 14070 occurs 12 times in the range in 5 rows (duplicates in same row are only counted once) Sub Frequency() 'Leo Heuser, 1 Nov. 2006 Dim CheckRange As Range Dim CheckRangeValue As Variant Dim Counter As Long Dim Counter1 As Long Dim CountFrequency() As Variant Dim CountRows() As Double Dim MaxValue As Double Dim MinValue As Double Dim RowColl As Collection Set CheckRange = Sheets("Sheet1").Range("B1:U1000") MinValue = 14060 MaxValue = 14100 ReDim CountFrequency(MinValue To MaxValue) ReDim CountRows(MinValue To MaxValue) CheckRangeValue = CheckRange.Value On Error Resume Next For Counter = 1 To UBound(CheckRangeValue, 1) Set RowColl = New Collection For Counter1 = 1 To UBound(CheckRangeValue, 2) If CheckRangeValue(Counter, Counter1) = MinValue And _ CheckRangeValue(Counter, Counter1) <= MaxValue Then CountFrequency(CheckRangeValue(Counter, Counter1)) = _ CountFrequency(CheckRangeValue(Counter, Counter1)) + 1 RowColl.Add Item:=CheckRangeValue(Counter, Counter1), _ key:=CStr(CheckRangeValue(Counter, Counter1)) End If Next Counter1 For Counter1 = 1 To RowColl.Count CountRows(RowColl(Counter1)) = CountRows(RowColl(Counter1)) + 1 Next Counter1 Next Counter On Error GoTo 0 End Sub -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurences in string | Excel Discussion (Misc queries) | |||
Count Occurences | Excel Discussion (Misc queries) | |||
Count # of Occurences | Excel Discussion (Misc queries) | |||
Count Occurences problem | Excel Discussion (Misc queries) | |||
Count occurences | Excel Programming |