Merging two lists of words together

[GTN]

Hey,

So basically I have to lists of words that I need to attach. Column A has a list of countries (200) and Column B has a list of activities (163).

Example:

A B
Algeria Running in
Cameroon Basketball in
Sudan Football in
Congo Walking in
United Kingdom Sailing in

I need to add the countries in column A to the end of column B. Example: Running in Algeria etc... But I need to add every country to every activity once in column B.

Example:
A B
Algeria Running in Algeria
Cameroon Basketball in Algeria
Sudan Football in Algeria
Congo Walking in Algeria
United Kingdom Sailing in Algeria
Running in Cameroon
Basketball in Cameroon
Football in Cameroon
Walking in Cameroon
Sailing in Cameroon
etc...

Is there any quick way I can do this?

Thanks for your help

[abousetta]

Hello and welcome to the forum,

Do you want to overwrite Column B or do you want the results in a new Column (e.g. C)?

[GTN]

Hey,

Thanks for your reply. it really doesn't matter . maybe in a new column is better.

[Jamesr571]

hello there,

in Column C use this code

Please Login or Register  to view this content.

This code will show the Contents of A2 & B2 (the " " will provide a space between words)

This code will work for Row 2 but can be dragged down.

Best Regards

Jamesr571

Please Consider:

If this reply provided the answers you were looking for please click on the star of this reply & Mark this thread as [Solved].

[GTN]

Thanks for your reply. But I need every country to be matched with every activity once. So, looking at the example, I need Algeria to be matched to every activity once and then move onto the next country.

[abousetta]

Maybe try this:

Standard Module

Please Login or Register  to view this content.

[GTN]

Thanks for your help. The code won't work. I get an error Compile error: Invalid iside procedure with a yellow arrow pointing to the first line of code in the macro ( the name of the macro ie: Sub country () ).

THanks again

[Jamesr571]

Hello GTN,

the code does work. What you need to do is when you open up visual basic open up a new Standard Module & copy in the code (code from abousetta).

Then in Visual Basic on the sheet you will hold this information use the following macro;

select from the 2 pull down lists; Left: Worksheet Right: Change

then type into that sub Call MergeLists

Please Login or Register  to view this content.

using this will basically run the macro that abousetta gave you everytime you fill in a cell on the worksheet.

Please see the attached for my example sheet

Book4.xls

Best Regards

Jamesr571

[abousetta]

It works fine for me. Have a look at the attachment.

[GTN]

Hey,

I got it to work. Thank you so much abousetta for your help and the code and Jamesr571 for your help.

I really appreciate it!

[Cutter]

Based on your last post, GTN, I'll mark your thread as SOLVED. Please remember to do that yourself in your future threads. Thanks.

[abousetta]

Thanks Cutter.

abousetta