FREQUENCY REPORT

vicktor schausberger · Oct 28, 2013

Hello everybody.

I have a dynamic Data I2:I2065 sheet 5 and I would like to see the frequency of this numbers but not, in the way the frequency function work, I need to organized according to the frequency and the output start at L20. Example:

frequency __150-151-152-160-165-170-171-172-173-180-
________________________________________________________________
data 111-125-131-140-155-120-110-084-250-303
-148-150-160-177-180-100-255-120
178-156-181-249-301-149

the idea is to get the frequency by groups and skip the empty sets, as you see there are not number with frequency 153 etc, the number jump to the set is populate. thank you for reading this.

p45cal · Oct 28, 2013

Explore Data tab, Analysis section, Data Analysis… Histogram.
Have your I2:I2065 as the Input range,
your Bin Range as where you have your 150-151-152-160-165-170-171-172-173-180, as a vertical range, anywhere but at L20,
your output range at L20
OK.

What version of Excel?

With your data I got:

Bin Frequency
150 12
151 0
152 0
160 3
165 0
170 0
171 0
172 0
173 0
180 3
More 6

vicktor schausberger · Oct 28, 2013

vicktor schausberger · Oct 28, 2013

I upload a images what exactly I am looking for, I hope work

NARAYANK991 · Oct 28, 2013

Hi ,

The link to the image is :

http://s1358.photobucket.com/user/m...rs[recent]=1&filters[publicOnly]=1&sort=1&o=0

Narayan

p45cal · Oct 28, 2013

I see the image, but I don't see the relation to the question at all.
The range I2:I2065 isn't included in any shape or form, nor is L20, and the highlighted numbers mean nothing to me.
Have you posted the right image?

vicktor schausberger · Oct 28, 2013

sorry, is the wrong pic. here it is the right one.

http://s1358.photobucket.com/user/murcielago27/media/forumpic1_zps996ad47f.jpg.html

p45cal · Oct 28, 2013

You say the data in column I is dynamic, so the results in rows 21 to 25 represent the different data over time?
It looks as if the data is getting smaller and smaller, so I guess the categories in row 20 depend on the first grouping of data, and don't change subsequently?
Have I got this right?

I wont be able to do much in the next 16 hours or so, but tomorrow..

vicktor schausberger · Oct 28, 2013

http://s1358.photobucket.com/user/murcielago27/media/forumpic1_zps996ad47f.jpg.html
as you see in this picture, is to take the frequency function and display de results by groups. and my data is dynamic every week increase. thanks. p45cal.

p45cal · Oct 29, 2013

You haven't really answered my questions; I don't understand how you arrive at the picture. So again,
what do the different rows on the picture represent? For instance, there's the number 178 sitting 2 cells below the number 152; what does it mean?
What do the numbers above and below the 178 represent?
How is the grouping decided upon?
A worksheet with the numbers that these data were made from would be good, so that I can see that I'm applying the correct logic. You can upload such a sheet here I think.

vicktor schausberger · Oct 29, 2013

http://s1358.photobucket.com/user/m...[user]=132371606&filters[recent]=1&sort=1&o=0
Sorry this is my last intent, the pic. you see here is JUST AN EXAMPLE my workbook is huge, so I just upload the smallest part, that why I said my data location, bin location and results location, and in the picture you see only the idea, in other word to display the frequency function results in different way, and the only way is with vba.

p45cal · Oct 29, 2013

I think I can do this.
What version of Excel are you using?
If you could upload a workbook with only 1 sheet with simply the raw data in column I, that would save me a lot of typing and time. If you've also got the expected results for that data set, that would be a bonus.

NARAYANK991 · Oct 29, 2013

Hi Vicktor ,

Can you confirm whether the attached file is something like what you want ?

Narayan

p45cal · Oct 29, 2013

It's fine, working on it.

p45cal · Oct 29, 2013

first draft that seems to work for you to check(but I still want to know which version of Excel you're using since I'll be creating a pivot table in the code. Note also that the results start at N390 at the moment and that the code works on the active sheet:

Code:

Sub Macro5()
Dim freqs()
Set xxx = Columns("I:I").SpecialCells(xlCellTypeConstants, 23)
vals = xxx.Value
vmax = Application.Max(vals)
vmin = Application.Min(vals)
bin = Evaluate("row(A" & vmin & ":A" & vmax & ")")
freqs = Application.WorksheetFunction.Frequency(vals, bin)
ReDim Preserve freqs(1 To UBound(freqs), 1 To 2)
For i = 1 To UBound(bin)
  freqs(i, 2) = bin(i, 1)
Next i
For i = 2 To UBound(bin)
  For j = UBound(bin) To i Step -1
    If freqs(j, 1) < freqs(j - 1, 1) Then
      temp1 = freqs(j, 1): temp2 = freqs(j, 2)
      freqs(j, 1) = freqs(j - 1, 1): freqs(j, 2) = freqs(j - 1, 2)
      freqs(j - 1, 1) = temp1: freqs(j - 1, 2) = temp2
    End If
  Next j
Next i
Set StartPosn = Range("N390")
: i = 1
Do
  StartPosn.Value = freqs(i, 1)
  rOffset = 1
  Do
    StartPosn.Offset(rOffset) = freqs(i, 2)
    rOffset = rOffset + 1
    i = i + 1
  Loop Until freqs(i, 1) <> freqs(i - 1, 1)
  Set StartPosn = StartPosn.Offset(, 1)
Loop Until i >= UBound(bin)
End Sub

vicktor schausberger · Oct 29, 2013

my version is 2010, I will check your code when I got home. thanks.

p45cal · Oct 30, 2013

Second draft, faster, allows selection of where the results will go, still uses in-memory arrays:

Code:

Sub blah2()
Dim freqs()
Set xxx = Columns("I:I").SpecialCells(xlCellTypeConstants, 23)
vals = xxx.Value
vmax = Application.Max(vals)
vmin = Application.Min(vals)
bin = Evaluate("row(A" & vmin & ":A" & vmax & ")")
freqs = Application.WorksheetFunction.Frequency(vals, bin)
ReDim Preserve freqs(1 To UBound(freqs), 1 To 2)
For i = 1 To UBound(bin)
  freqs(i, 2) = bin(i, 1)
Next i
For i = 2 To UBound(bin)
  For j = UBound(bin) To i Step -1
    If freqs(j, 1) < freqs(j - 1, 1) Then
      temp1 = freqs(j, 1): temp2 = freqs(j, 2)
      freqs(j, 1) = freqs(j - 1, 1): freqs(j, 2) = freqs(j - 1, 2)
      freqs(j - 1, 1) = temp1: freqs(j - 1, 2) = temp2
    End If
  Next j
Next i
'determine size of array:
i = 1
ColCount = 0
Do
  myMax = 1
  ColCount = ColCount + 1
  Do
    i = i + 1
    myMax = myMax + 1
  Loop Until freqs(i, 1) <> freqs(i - 1, 1)
  If myMax > Max Then Max = myMax
Loop Until i >= UBound(bin)
Dim Results()
ReDim Results(1 To Max, 1 To ColCount)
 
 
i = 1: c = 1
Do
  r = 1
  Results(r, c) = freqs(i, 1)
  r = r + 1
  Do
    Results(r, c) = freqs(i, 2)
    r = r + 1
    i = i + 1
  Loop Until freqs(i, 1) <> freqs(i - 1, 1)
  c = c + 1
Loop Until i >= UBound(bin)
Dim Destn As Range
On Error Resume Next
Set Destn = Application.InputBox("Select the cell where do you want the results", "Location of result table", Type:=8)
On Error GoTo 0
If Destn Is Nothing Then
  MsgBox "Aborted"
Else
  Destn.Resize(UBound(Results), UBound(Results, 2)).Value = Results
  Application.Goto Destn
End If
End Sub

p45cal · Oct 30, 2013

A little slower, but perhaps a little bit more robust, uses a pivot table on a newly created hidden sheet which gets deleted afterwards. Should work in Excel 2010:

Code:

Sub blah1()
Set DataSht = ActiveSheet
Set TempSht = Sheets.Add(After:=Sheets(Sheets.Count))
TempSht.Visible = 0
DataSht.Columns("I:I").SpecialCells(xlCellTypeConstants, 23).Copy TempSht.Range("A2")
Set xxx = TempSht.Columns(1).SpecialCells(xlCellTypeConstants, 23)
vals = xxx.Value
vmax = Application.Max(vals)
vmin = Application.Min(vals)
bin = Evaluate("row(A" & vmin & ":A" & vmax & ")")
Set BinRange = TempSht.Range("B2").Resize(UBound(bin))
BinRange.Value = bin
freqs = Application.WorksheetFunction.Frequency(vals, bin)
BinRange.Offset(, 1).Value = freqs
TempSht.Range("B1:C1").Value = Array("bin", "freq")
Set PT = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TempSht.Name & "!R1C2:R284C3", Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:=TempSht.Name & "!R1C6", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14)
With PT
  .AddDataField PT.PivotFields("bin"), "Sum of bin", xlSum
  .ColumnGrand = False
  .RowGrand = False
  With .PivotFields("bin")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("freq")
    .Orientation = xlColumnField
    .Position = 1
  End With
  Set PTRngToCopy = Union(.ColumnFields(1).DataRange, .DataBodyRange)
  Set PTRangeDest = PTRngToCopy.Offset(PTRngToCopy.Rows.Count + 9)
  PTRangeDest.Value = PTRngToCopy.Value
End With
 
PTRangeDest.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Results = PTRangeDest.CurrentRegion.Value
Dim Destn As Range
On Error Resume Next
Set Destn = Application.InputBox("Select the cell where do you want the results", "Location of result table", Type:=8)
On Error GoTo 0
If Destn Is Nothing Then
  MsgBox "Aborted"
Else
  Destn.Resize(UBound(Results), UBound(Results, 2)).Value = Results
  Application.Goto Destn
End If
Application.DisplayAlerts = False
TempSht.Delete
Application.DisplayAlerts = True
End Sub

p45cal · Oct 30, 2013

Oops:
1. blah2 correction (it was missing the last column of results)
2. Apologies to NARAYANK991, I thought his file was was actually Viktor's, anyway, it helped me a lot to check I was doing things right. Narayank, do your formulae miss out the last column too?

The corrected blah2:

Code:

Sub blah2()
Dim freqs()
Set xxx = Columns("I:I").SpecialCells(xlCellTypeConstants, 23)
vals = xxx.Value
vmax = Application.Max(vals)
vmin = Application.Min(vals)
bin = Evaluate("row(A" & vmin & ":A" & vmax & ")")
freqs = Application.WorksheetFunction.Frequency(vals, bin)
ReDim Preserve freqs(1 To UBound(freqs), 1 To 2)
For i = 1 To UBound(bin)
  freqs(i, 2) = bin(i, 1)
Next i
For i = 2 To UBound(bin)
  For j = UBound(bin) To i Step -1
    If freqs(j, 1) < freqs(j - 1, 1) Then
      temp1 = freqs(j, 1): temp2 = freqs(j, 2)
      freqs(j, 1) = freqs(j - 1, 1): freqs(j, 2) = freqs(j - 1, 2)
      freqs(j - 1, 1) = temp1: freqs(j - 1, 2) = temp2
    End If
  Next j
Next i
'determine size of array:
i = 1
ColCount = 0
Do
  myMax = 1
  ColCount = ColCount + 1
  Do
    i = i + 1
    myMax = myMax + 1
  Loop Until freqs(i, 1) <> freqs(i - 1, 1)
  If myMax > Max Then Max = myMax
Loop Until i >= UBound(bin)
Dim Results()
ReDim Results(1 To Max, 1 To ColCount + 1)
i = 1: c = 1
Do
  r = 1
  Results(r, c) = freqs(i, 1)
  r = r + 1
  Do
    Results(r, c) = freqs(i, 2)
    r = r + 1
    i = i + 1
  Loop Until freqs(i, 1) <> freqs(i - 1, 1)
  c = c + 1
Loop Until i > UBound(bin)
Dim Destn As Range
On Error Resume Next
Set Destn = Application.InputBox("Select the cell where do you want the results", "Location of result table", Type:=8)
On Error GoTo 0
If Destn Is Nothing Then
  MsgBox "Aborted"
Else
  Destn.Resize(UBound(Results), UBound(Results, 2)).Value = Results
  Application.Goto Destn
End If
End Sub

NARAYANK991 · Oct 30, 2013

Hi ,

Sorry , that was my mistake ; the formula in row 20 ( N20 , O20 ,... ) was :

=IF(MAX($N$20:AC20)+1<MAX($L$2:$L$304),AC20+1,"")

I have now changed it to :

=IF(MAX($N$20:AC20)+1<=MAX($L$2:$L$304),AC20+1,"")

and two more numbers ( 120 and 256 ) appear in that column ( column AD ).

Narayan

vicktor schausberger · Oct 30, 2013

NARAYANK991 thanks, good. I click like for you. the thing is I have to write it every time, reason I want vba.
p45cal when I tried to run , give me an error object define. so. can you send me a workbook example please, maybe I make a mistake.

NARAYANK991 · Oct 30, 2013

Hi Vicktor ,

In case p45cal does not post his revised example in some time , I'll upload a workbook.

Narayan

p45cal · Oct 30, 2013

Viktor, if you could post a workbook with your data (as already suggested in msg #16), I will add the macro(s), check them, and it will also help me understand what kind of data we are dealing with, and it will let me cater for it properly (I did expect the macros to work "out of the box").

vicktor schausberger · Oct 30, 2013

here it is my workbook , I click on upload a file, I don't know this forum I am new. I hope something upload.
'

p45cal · Oct 30, 2013

Attached is your file with only one of the macros in because you uploaded an .xls file, not an Excel 2010 file and the macro that uses a pivot table is not compatible with .xls files. (I may try and upload a 2010 xlsm file later (especially to try to handle zeroes properly.)
It will have gone wrong because the data was in column H instead of column I, and because the data are the result of formulas, not just values.

Another potential fault could be that zeroes miught be included in the source data.. will this EVER be the case?

FREQUENCY REPORT

Member

Well-Known Member

Member

Member

Excel Ninja

Well-Known Member

Member

Well-Known Member

Member

Well-Known Member

Member

Well-Known Member

Excel Ninja

Attachments

Well-Known Member

Well-Known Member

Member

Well-Known Member

Well-Known Member

Well-Known Member

Excel Ninja

Attachments

Member

Excel Ninja

Well-Known Member

Member

Attachments

Well-Known Member

Attachments