Help with aligning website URL and email addresses with same domain

[papusale]

Hi everyone!

I need help aligning website URL's with their corresponding email addresses (based on domain).
The URLs and emails had been gathered from separate sources, which is why they are not in order.

So for example if there's an URL "www.example55.com/example/124", I would like it to horizontally align with "name@example55.com" email.

PS: I have searched Google, having one answer found but with a non-working formula unfortunately .

[Special-K]

To solve this we're gonna need more than one example.

[LokeshKumar]

Could you upload the sample workbook with more examples...

[papusale]

Thank's for replying Special-K.

I've attached an image of the list.

example image.JPG

As you can see, the left column has the URL's and the right has emails (however not aligned correctly).
What I want is that their corresponding emails are aligned (based on domain ).

Also, not sure if this makes a difference; some URLs will not have a corresponding email address, thus they will be left blank.

[papusale]

Sure LokeshKumar, I will attach the workbook.

Note: Up until entry 2079, I have aligned the URLs and emails manually by hand, and that's a very long process!

[Special-K]

Is there only ONE email domain per website, ie http://www.loudness.se, will that only have ONE related email in the email list?

[papusale]

Is there only ONE email domain per website, ie http://www.loudness.se, will that only have ONE related email in the email list?

Yes, only one email per URL.

[popipipo]

Try this one
=IFERROR(VLOOKUP("*"&SUBSTITUTE(SUBSTITUTE(B2,"http://",""),"www.",""),$C$1:$C$5000,1,0),"")

[Kyle123]

I reckon it's more resilient to do it the other way round then Vlookup the data back in:

Please Login or Register  to view this content.

[papusale]

Try this one
=IFERROR(VLOOKUP("*"&SUBSTITUTE(SUBSTITUTE(B2,"http://",""),"www.",""),$C$1:$C$5000,1,0),"")

Thanks very much! But unfortunately I get a formula error message:

error1.JPG

Not sure if this helps; I'm using the Finnish character format; where the Finnish format use "," to separate decimals, the US format uses "." to separate decimals

[papusale]

I reckon it's more resilient to do it the other way round then Vlookup the data back in:

Please Login or Register  to view this content.

Sorry for my ignorance, but where do I insert this code?
I only know the basics of Excel, so I would require a bit of help here.
Thanks a lot!

[Kyle123]

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[JCott]

Do you have auto-format enabled? it could be an issue

[papusale]

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Thanks. I have done everything you said and ran the "sorty" macro, but nothing happened. I have enabled all macros in the macro security options of Excel.

[popipipo]

I get a formula error message

Here the file without an error

[Kyle123]

Have you had a look on sheet2

[papusale]

Here the file without an error

Thank you! This worked!

The problem seemed to be in the format of commas. The formula given to me via this discussion had commas in some parts, while the file you have given had semicolons in the exact same parts. I guess it had to do with my local machine's format as I expected.