Concatenate function

**floricita** · 05-24-2010, 07:17 AM

Hi there,

I'm trying to improve my quite basic excel skills and have therefore decided to join this forum.

Not sure if anyone can help me, but today I encountered the following problem.

I have two columns.

Column A has a list of each shop in the business organisation. Column B has the name of the shop's competitor.

However, if there is more than 1 competitor for that shop, the shop number is repeated underneath with the corresponding competitor name in column B.

So it looks like this

Shop no Competitor name
1 A
1 B
2 A
2 B
2 C
3 B

Etc etc for thousand of shops.

What I need in a seperate column is the shop no and in the next column the names of all the competitors for that shop, so look like this

Shop no Competitor names
1 A B
2 A B C
3 B

I had a play with this but just can't get it to work properly.

Any help on this would be greatly appreciated.

Cheers

**Marcol** · 05-24-2010, 09:09 AM

Try this as a VBa option

Please Login or Register  to view this content.

I have attached a small demo workbook

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**floricita** · 05-24-2010, 09:29 AM

Hi there and thanks for the response.

I know it may sound silly, but with macros/vba I have very basic knowledge and normally just press the record mode to record the VBA.

I've never just written VBA by itself and am not sure how exactly to record it in written form and then how to apply it to the work sheet?

Thank you.

**floricita** · 05-24-2010, 09:34 AM

Ok, I kind of cheated and stepped into one and cut and pasted this code, but when I ran it nothing happenned, and no errors either???

**floricita** · 05-24-2010, 09:38 AM

Oh my god, sorry I am very much a novice at this, I had it on break mode, now I put it on normal mode and ran it and it works.

Thank you so much, I am so happy.

**ChemistB** · 05-24-2010, 09:39 AM

Go to the VBA Editor (ALT F11) and INSERT>Module. Then paste the code in that window. Did that help?

**Marcol** · 05-24-2010, 10:03 AM

The proceduire is as follows

1/. Open the VBa Editor
Tools > Macro > Visual Basic Editor (or Press Alt+f11)

2/. In the VBa Editor
Insert > Module

3/. In the new module pane that appeared
Paste ALL of the code from post #2

4/. In Excel
Tools > Macro > Macros....
Select the desired macro, in this case, "ConcatenateNames"
Press Run.

Alternatively
1/. Open the demo workbook "ConcatenateNames.xls"

2/. Open the VBa Editor as above

3/. If the module is not already showing
In the Project pane (Ctrl+R) double click on Module 1

4/. File > Export File....
Save the module to your desired location

5/. With your workbook
In the VBa Editor
File > Import File....
Select the saved file (it will be named Module1.bas unless you gave it some other name)
Press Open

You may have to adjust the column references to suit your worksheet.

If you run into any problems, post a sample Workbook and I will adjust the code to suit.

Cheers.

P.S.
I missed all of the intervening posts, none of them were showing when I posted this!!! ????