excel help

hueknight1969

Hi

Is there a way to get excell to look at a group of cells and tell you the frequency of all the values without typing a specfic range or exactly the value. eg
1
1
2
2
2
4
6

1=2 times; 2=3times; 4=1 times; 6=1 times

I have 2800 entries to sort out, im sure this can be done (i have looked on google with no luck)

thanks

hugh

Cornucopia

Yes, there's probably several ways to do it.

I would do this:-

- Copy the list of values
- Sort the copied list in ascending order
- Add a de-duplication formula alongside the copied list (see below)
- Create a de-duplicated new list
- Add a counting formula to the de-duplicated list

Typical de-duplication formula for a vertical list:-

in column B, add: =IF(A1=A2,"",A1) to every cell next to a source value in column A

Typical counting formula for a vertical list:-

=COUNTIF("=" & C1, A: A)

WaywardDriver

Insert a heading (A say) in the row above the numbers and Insert a PivotTable, drag the heading to both the Rows and Values sections, click where it says Sum of A, click Value Field Settings and change to Count.

Tom99

Yes a PivotTable will do it.

stragglebod

+1 pivot table

rmg1

Just to add in my 2p-worth.
A pivot table will certainly do what you want.
If you want to re-use it with ranges of varying sizes, research dynamic data ranges and then attach the pivot-table to one of those.