How do you sort columns so that they match up with other columns with like data?

I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.

Good luck and I hope somebody has an answer!


Column A
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

Column B
www.courvelletoyota.com
www.ajdohmann.com
www.bonifacehiers.com
www.bonifacehierschryslerdodge.net
www.charliethomaschrysler.com
www.silverstarauto.com
www.tustinlexus.com
www.weircanyonhonda.com
www.lawsonchevy.com
www.autonation.com
www.northtownechevrolet.com
www.poageautoplaza.com
www.zimbrick.com
www.zimbrick.com
www.southcoastmitsubishi.com
www.hallauto.com
www.alsanchezauto.com
www.atlantatoyota.com
www.fernandezhonda.com
www.fletcherauto.com

Please read our forum rules and then adjust your title according to Rule # 1.

Thanks for fixing the title....

Possibly you can use a formula that aligns the matches and then you can copy|paste Special >> Values over the original...

So if your websites are in column A and your emails are in column B, then in C1, formula would be:

=INDEX($B$1:$B$20,MATCH(TRUE,ISNUMBER(SEARCH(MID(A1,FIND("@",A1)+1,255),$B$1:$B$20)),0))

adjust ranges to suit.

The formula is an array formula and so must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear.

Then copy down the whole list.

See attached sample.

Did you adjust the ranges to accomodate the 9k records?

Did you confirm the formula with CTRL+SHIFT+ENTER?

You don't actually copy/paste the formula, you fill it down.

After you successfully entered the first formula, click and hold the little black square at the bottom right corner of the cell, then drag it down as far as you want to fill in the formula.

This should retain the curly brackets around the formula.

Well...actually....if you copy the cell that the formula is in and then paste it to the cells underneath...it should work too. But you can't copy the formula itself and paste it in the cells or else you will have to reconfirm each one...