OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to Copy without repetitions or find repetitions?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
pawel.lang
General User
General User


Joined: 26 Jan 2005
Posts: 5

PostPosted: Mon May 30, 2005 10:10 pm    Post subject: How to Copy without repetitions or find repetitions? Reply with quote

I have a column with a plenty of repeated names.

What I need is to list those names (without repetitions) and then to count how often they do occure in the list.

Any sugestion? I'm in desperate hour...
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Mon May 30, 2005 10:41 pm    Post subject: Reply with quote

QUICK AND DIRTY:-

Say your list is in B2:B13

In col D "=COUNTIF($B$2:$B2;B2)" (without quotes) and drag to bottom of list gives you the number of times the name has occurred so far in the list.

In Col C "=COUNTIF($B$2:$B$14;B2)" (and drag) gives you the total number of times the name occurs in the list.

Select your list, including cols C & D and headings.
Data menu, click Auto Filter
Select "1" from tdhe drop down list in col D (this appears at the heading when you click Auto Filter).

You should now have a list of names (appearing once only) and how often they appear (before applying the filter). Select the appropriate columns.
Copy
Paste Special (uncheck everything except "String" and "Number")

This should give you the list you want.
Note: because you have not posted the formulas, the list will be static. You should maintain the original list and repeat this exercise when you need to generate the report again. (Right off the top of my head I can't think of an easy way to come up with a dynamic list that doesn't contain zero-length strings where the repeated names would be.)

Hope this helps.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Mon May 30, 2005 10:45 pm    Post subject: Reply with quote

I just read my reply. It's real easy to miss the "$" signs in the COUNTIF formulas. What I've written is correct (I think), but pay attention!
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
pawel.lang
General User
General User


Joined: 26 Jan 2005
Posts: 5

PostPosted: Mon May 30, 2005 10:46 pm    Post subject: Reply with quote

Great! THX! Very Happy

However it doesn't count the names. Sad

Its not relevant by now 'couse I can handle it by myself. Once more thx!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group