Sort many entrys in to uniqe number

[bobhc]

Morning

I have tryied to get pivot tables to work but I am having a bit of trouble with them so I am going to use straight forward charting tools. I have a lot of coloums re accidents and will copy/paste the coloums I need into a new work sheet to get the information for the relevant chart, what I need help with is I have a coloum sush as:-


Body Part

Ankle

Ankle

Back

Back

Back

Eye

Eye

Foot

Foot

Hand

Hand

Hand

Hand

Hand

Hand

Hand

Hand

Hand


I need to know the formula to list the number of Body parts and the number:-

foot 14

hand 12

head 4

eye 5


and so on

 

[NARAYANK991]

Hi ,


You can use the COUNTIF function ; a formula like :


=COUNTIF(A2:A19,"Hand")


will return the number of occurrences of "Hand".


if you want to have just one formula , which will display the counts of each of the body parts , use the following formula in column B i.e. put this formula in B2 , and copy it down to the remaining relevant cells in column B :


=IF(A2=A1,"",COUNTIF(A2:$A$19,A2))


This assumes that the list will be sorted so that all like body parts are together.


Narayan

 

[bobhc]

Good day NARAYANK991

What I final did was as follows

I sorted my column of data, column ”A” then in the next column “B” I ran this formula, =IF((A2)<>"",1,0), to the get the number 1 against all entry’s. I then ran, in column “C” the sum function on the top most cell next to the number’s to get the sub totals. I then ran the “find duplicates” this removed all the cells except the ones with the totals in. I hid column “B” and then used the data to make a graph.

I realise that to Excel experts it is a messy way of doing it but it worked for an old guy.

 

[SirJB7]

Hi, bobhc!

Give a look at this link:

http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/

In the first part there is an example of how to create a unique list from a list with duplicates.

Then goto to E4, add =IF(D4="","",COUNTIF(C$4:C$22,D2)), copy down thru E22, et voila!

Go on learning new tricks, old man...

Regards from a very young man!