Word Count

[Gigafreak]

Hello Group

I am a new subscriber to the group and a fairly new Excel (2002) user.
What I would like to know is how can I count the number of occurences
of words in a column.

To explain what I mean I'll try and illustrate my worksheet and what I
am using it for.

In column G of each worksheet I have a list of places that have been
visited, this list is added to and each worksheet is a new month. The
list of place names may include place names and, of course duplicate
place names eg;

Bristol
Bath
Swindon
Exeter
Bristol
Cardiff
Newport
Bristol

What i would like to do is create in column H a result that shows the
number of times each place name occurs in that specific month. From
there I could probably work out how to create a chart to illustrate
the monthly/annual place visit statistcal reprosentation.
Any ideas comments would be greatly appriciated.

Craig

 

[Nick Hodge]

Craig

Perfect job for a pivot table. The only difference I would make in this
scenario would be to keep the trips on one sheet and add a date column next
to each entry.

To create you basic pivot take the options Data>Pivot table and using your
whole data range place the town column onto the row field area and then add
it again to the value area and this should be set automatically to 'Count'.
From this you can now simply, from the pivot table toolbar, select the pivot
chart option to chart from this data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

In column "H" place this formula in each cell for each variation.
="Bristol " & CountIf(G1:G1000,"Bristol")
="Bath " & COUNTIF($C$217:$C$230,"Bath")
The result will be (from example list) Bristol 3
and Bath 1
etc.

 

[Guest]

I gave two formulae but failed to make that clear. I'm sure you knew what I
was thinking.

You can also add this:
& " Occurrences" to produce "Bristol 3 Occurrences" etc.

 

[Gigafreak]

Craig

Perfect job for a pivot table. The only difference I would make in this
scenario would be to keep the trips on one sheet and add a date column next
to each entry.

To create you basic pivot take the options Data>Pivot table and using your
whole data range place the town column onto the row field area and then add
it again to the value area and this should be set automatically to 'Count'.
From this you can now simply, from the pivot table toolbar, select the pivot
chart option to chart from this data

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)










- Show quoted text -

Thanks Nick, could I have a step by step guide for this please? This
is new to me. I have to keep the place names as they are as I keep a
record of other items for each visit such as time of arrival, time
completion, ETA and so on with a rolling average of each. I would
like to just add a count of the places. What i'd like to do is
produce a column with the places visited and the resultant count in
the next column, hope I'm making sense Many thanks

 

[Guest]

I placed the list of names in Column B as a reference for the formula.
Bristol was in B3.
The following was in H2

=B2 & " " & COUNTIF($C$2:$C$300,B2) & _
IF(COUNTIF($C$2:$C$300,B2) > 1, " Occurrences", _
" Occurrence")

Result Bath 1 Occurrence

The following was in H3

=B3 & " " & COUNTIF($C$2:$C$300,B3) & _
IF(COUNTIF($C$2:$C$300,B3) > 1, " Occurrences", _
" Occurrence")

Result Bristol 3 Occurrences

etc.

 

[Nick Hodge]

Craig

Solution remains the same

Highlight your data, select Data>pivot table and pivot chart report, select
Excel list and pivot table (two default options) and click next.

Make sure the range in step two is the range of your data and finish

Now you should have an empty grid. Drag from the field list on the right the
data containing the towns to the row field area and drop, drag the same
field to the value or data area and drop. (because it's text it should
default to count). Click finish and you should have a great little table
with towns and count of visits. From here there's a ton of other stuff you
can do with dates, times, etc and these should already be in the field
chooser on the right.

When you add data you will either need to re-invoke the wizard and go back
and change the range or you could make a range name on which it is based and
make it dynamic.

There is info on pivot tables all across the internet, mine is here.

http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Gigafreak]

Craig

Solution remains the same

Highlight your data, select Data>pivot table and pivot chart report, select
Excel list and pivot table (two default options) and click next.

Make sure the range in step two is the range of your data and finish

Now you should have an empty grid. Drag from the field list on the right the
data containing the towns to the row field area and drop, drag the same
field to the value or data area and drop. (because it's text it should
default to count). Click finish and you should have a great little table
with towns and count of visits. From here there's a ton of other stuff you
can do with dates, times, etc and these should already be in the field
chooser on the right.

When you add data you will either need to re-invoke the wizard and go back
and change the range or you could make a range name on which it is based and
make it dynamic.

There is info on pivot tables all across the internet, mine is here.

http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)







- Show quoted text -

Nick, thats awesome!!! Thank you very much

Craig

 

[Nick Hodge]

<snipped>

Craig

Glad it worked

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk