sorting information in MS Excel 1

[woody2002]

Hi,

I have a small problem that I was hoping someone could shed some light on....

I have a long list of over 28000 email addresses and I need to sort them in a certain way. I have a list of companies that have 'opted out' of receiving information via email and I need to flag these. As there are so many addresses there is no way I can go through each row individually as this would take forever! I can't sort the column by email address as it sorts on the first bit (firstname.surname) and I need it sorted on the info after the @ (companyname.co.uk)

I need to find a way of flagging them in Excel the query being something along the lines of: If email = '%@companyname.co.uk' (% being a wildcard like you would use in SQL) then put N/A, then I could sort on email
address and pull out all N/A's

Is there a simple way of doing this? I've started to look at LOOKUP, but it's proving a little difficult!

Help me!!

Thanks Ian

 

[Wray69]

Hi Ian,

You could put a column next to the email address and put a formla like this in =RIGHT(A1,LEN(A1)-FIND("@",A1)) that would pull out every thing after the @ and then do something like a vlookup to place n/a next to the companys you want to take out.

Hope this helps out...

Regards -

Wray

 

[DaleWatson123321]

woody,

Excel's "database extraction" function (Data - Filter - Advanced Filter) does have the ability to selectively extract data based on finding data within a string.

If you'd like to email me a small sample of your file, I'll set it up for you and return the file.

The result will be... at the click of a button, your required data will be extracted to a separate sheet.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Gavona]

Why not create a new column containing the companyname.co.uk bit. Use text to columns with a delimiter of @.

You could do the same with a formula, using Functions Right() and Find() but thats not really necessary.

 

[Wray69]

Actually after you pull the company name into the next column using =RIGHT(A1,LEN(A1)-FIND("@",A1)) then in the column next to that do something similar to this
=IF(D1="companyname.co.uk","N/A"," &quot and auto fill it down and all the address with companyname.co.uk will marked with an N/A.

Regards

Wray

 

[woody2002]

Cheers for all you help....

I've had a look at everything you have suggested, and both Wray and Gavona have helped me to remove the 'name@' section of the email address easily (I actually just figured it out when the 'notify email popped up!) so the information is now easier to sort, but is there a now a way similar to what Wray has suggested in his second post to place the 'N/A' against a multiple of different addresses, for example:

Sample List email addresses

directline.com
greenflag.com
gcm.com
lombard.co.uk
lombarddirect.com
natwest.com
rbsmentor.co.uk
natwestblack.com
natwestprimeline.com
rbsadvanta.co.uk
rbscs.co.uk
adamandcompany.plc.uk
angeltrains.com
citizensbank.com
coutts.com
gruposantander.com
oneaccount.com
rbos.co.uk
rbsint.com
rbsmarkets.com
rbsprivatebanking.co.uk

Now from this list lets assume that everyother company has opted out, so I need to flag these somehow (it isn't every other, but you get my point!) If I use Wrays second suggestion I can only search for one specific email address, but I need to search for 10 - do you get what I mean??

Thanks...

Woody

PS Thanks for the offer Dale, I'm gonna try and figure this one out first (with the help of you guys of course!) but I might take you up on your offer!!

 

[Wray69]

Hey Woody,

Try this make a seperate table with the ones you want to omit like such;

greenflag.com
lombard.co.uk
natwest.com
natwestblack.com
rbsadvanta.co.uk
adamandcompany.plc.uk


then you can use a formula like this;

=IF(ISNA(VLOOKUP(A1,$E$2:$E$7,1,FALSE))," ","N/A&quot

Regards -

Wray

 

[woody2002]

Cheers Wray, you've been a real help

W