Counting cells that have the same data

[andy.williams1971]

I have a spreadsheet with 31,000 rows, column A has a number that is
duplicated numerous times. There are 7500 different numbers in the
column, I want to know how many times each number appears. Is this
possible?

 

[Guest]

One quick way is to use .. a pivot table (PT) ?

With a col label in A1 (eg: Num), select col A, then click Data > Pivot
table & Pivot Chart Report, click Next > Next. Click Layout in step 3, drag
Num and drop in ROW area, drag Num again and drop in DATA area (appear as
"Count of Num"). Click OK, then Finish. Go to the PT sheet (just to the
left), and you should see the required results -- a listing of all the unique
numbers in the source's col A, and the count for each number next to it (up
in secs ! <g>).

 

[Bob Phillips]

B1: =A1

C1: =COUNTIF(A:A,A1)

and copy down

Then select B:C, copy, Edit>Pastespecial

Then Data>Filter>Advanced Filter, select the Copy to another location
button, check the Unique Records box, select a location to copy to.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[andy.williams1971]

Excellent, thanks

B1: =A1

C1: =COUNTIF(A:A,A1)

and copy down

Then select B:C, copy, Edit>Pastespecial

Then Data>Filter>Advanced Filter, select the Copy to another location
button, check the Unique Records box, select a location to copy to.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)