I'm sure this will be easy for your bods out there so going to call this Excel 101.
Question 1: I have 3500 email addresses in one column. However I know that they are not all unique, therefore I want to be able to see how many unique emails I have.
I thought I could do something like searching on the first 8 characters but currently I'm failing with the code of =left(AM2,8) - with AM2 being the column and 8 being the characters.
So how do I solve this?
Question 2: However, each email address is associated to a date. I've created 2 additional columns, Day & Month and have now been able to filter these fine. So when I now create a pivot table I can see what email addresses are for what day. However, as above, I need unique email addresses for the day, so is there anything further I need to consider?
Thanks
Excel 101
- murf
- FISO Viscount
- Posts: 109450
- Joined: 13 Oct 2005, 18:28
- Location: here
- FS Record: Once led TFF. Very briefly.
- FPL:
- Contact:
Re: Excel 101
There must be simpler ways but...
a) sort them into alphabetical order (identical ones will be next to each other), say in column A
b) In column B, ask If A2=A1,1,"" (i.e. is this one identical to the previous)
c) Total the 1s
d) Deduct from total count
a) sort them into alphabetical order (identical ones will be next to each other), say in column A
b) In column B, ask If A2=A1,1,"" (i.e. is this one identical to the previous)
c) Total the 1s
d) Deduct from total count
- Knulpuk
- Dumbledore
- Posts: 8986
- Joined: 05 Dec 2005, 13:22
- Location: MTA (Mid Table Anonymity)
- FS Record: Ultra ultra consistent (see above)
- FPL:
Re: Excel 101
re 1 number of ways - I would just sort alpahabetically
so column a is a list of sorted addressesstarting cel A1 then in column B in cel B1 type "=B2" then drag it down - then your "true" returns will be duplicates - false will be unique.
Cut past special values and then sort on true / false column.
so column a is a list of sorted addressesstarting cel A1 then in column B in cel B1 type "=B2" then drag it down - then your "true" returns will be duplicates - false will be unique.
Cut past special values and then sort on true / false column.
- Knulpuk
- Dumbledore
- Posts: 8986
- Joined: 05 Dec 2005, 13:22
- Location: MTA (Mid Table Anonymity)
- FS Record: Ultra ultra consistent (see above)
- FPL:
Re: Excel 101
2010 Excel has of course a remove duplicate button - you could just use that
Data > Remove duplicates
I assume you do not have that
Data > Remove duplicates
I assume you do not have that
- Rob
- Grumpy Old Man
- Posts: 3638
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: Excel 101
Two ways spring to mind for your first question.stuboy wrote:Question 1: I have 3500 email addresses in one column. However I know that they are not all unique, therefore I want to be able to see how many unique emails I have.
I thought I could do something like searching on the first 8 characters but currently I'm failing with the code of =left(AM2,8) - with AM2 being the column and 8 being the characters.
So how do I solve this?
1 - Select the 3,500 email addresses and then do an advanced filter (Data>Filter>Advanced Filter), click the 'Copy to another location' option and then make sure that the 'Copy to:' field is set to the first cell of an empty colum (E1, for example). Tick the option 'Inique records only' and then click okay. You should then have a list of the email adresses excluding duplicates.
2 - Use a countif formula. If the first email address is in A2 then in C2 put =COUNTIF(A$2:A2,a2) copy this formula down to the end of your list. You can then do a countif on that column as follows =COUNTIF(C:C,"1")
-
- Dumbledore
- Posts: 5457
- Joined: 01 Sep 2010, 12:25
- Location: Amsterdam
- FPL:
Re: Excel 101
After reading the first 2 posts I was about to say that the reason I called it excel 101 was so you would talk to me a like a child - when in fact you're talking to me like I'm a scientist.
Knulpuk, I'm still not sure how in cell B1, clicking =B2 comes up with anything.
Anyhow, then I saw this....
Thanks chaps!
Knulpuk, I'm still not sure how in cell B1, clicking =B2 comes up with anything.
Anyhow, then I saw this....
Knulpuk wrote:2010 Excel has of course a remove duplicate button - you could just use that
Data > Remove duplicates
I assume you do not have that
Thanks chaps!
View Latest: 1 Day View Your posts