To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Excel 101

A forum to discuss or share knowledge of spreadsheets.
Post Reply
stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Excel 101

Post by stuboy »

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

User avatar
murf
FISO Viscount
Posts: 109448
Joined: 13 Oct 2005, 18:28
Location: here
FS Record: Once led TFF. Very briefly.
Contact:

Re: Excel 101

Post by murf »

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

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: Excel 101

Post by Knulpuk »

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.

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: Excel 101

Post by Knulpuk »

2010 Excel has of course a remove duplicate button - you could just use that

Data > Remove duplicates

I assume you do not have that

User avatar
Rob
Grumpy Old Man
Posts: 3638
Joined: 24 Jul 2009, 10:54
Location: Poster formerly known as SOBD
FS Record: WC2010 FISODAS Champion!!

Re: Excel 101

Post by Rob »

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?
Two ways spring to mind for your first question.

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")

stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Re: Excel 101

Post by stuboy »

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....
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
:oops: :oops:

Thanks chaps!

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”