pimmo2000

iTrader: (6)

I have a large sheet that I need to extract some data from ..

Its a report of user activities and each line contains the username and the activity ..


so for example

Pimmo Internet
Pimmo Break
Pimmo Internet
Pimmo Internet
Pimmo chess
Pimmo sex


etc

I want to be able to count the number of times Pimmo appears with each type of activity .. BUT I don't have a list of all the activities ..



So I need

Pimmo and internet appeared 3 times
Pimmo and sex appeared 1 time


if you get me ...

SQL I could do it... excel I haven't a clue ..

Puff The Magic Wagon!

iTrader: (2)

=COUNTIF(A:A,"Pimmo Internet")

Puff The Magic Wagon!

iTrader: (2)

Ah - I see...

pimmo2000

iTrader: (6)

you see ?

Puff The Magic Wagon!

iTrader: (2)

If data is in one column, starting A1 and all you want to do to find the Pimmo instancs and what instances they are of, then in B1

=IF(LEFT(A1,5)="Pimmo","PMO","")
Enter
Drag down all the data (or double-click small black dot in B1 left corner)
Ctrl A to select the data
Insert Pivot Table
Drag the data field down to Values and change to count
Drag the data field down to Row Labels

That will give you grouped/counted data (all)

Drag PMO field to Report Filter then click on the filter (on the table not the PivotTable Field List) and select PMO only to view.

That will then give you a list of all PMO (Pimmo values), the items and the frequency...

Puff The Magic Wagon!

iTrader: (2)

Sorry, add in a line at the top and enter headings - Data & Pimmo
Data starts in A2 & formula for PMO in B2

Puff The Magic Wagon!

iTrader: (2)

davyboy

Pivot tables.

Put a number 1 next to each entry (in the next cell) and add headers

Pimmo Internet 1
Pimmo Break 1
Pimmo Internet 1
Pimmo Internet 1
Pimmo chess 1
Pimmo sex 1

Data/Pivot table....select data, new worksheet etc

Drag and drop headers in to chart

pimmo2000

iTrader: (6)

Thank you .. I'll give it ago today