Macro to search multiple values corresponding to another one and concatenate them in a different column

scruffy89

New Member
Joined
May 27, 2015
Messages
6
Hello everyone! I am new to excel so I kinda need some help.
I have 3 columns A , b and C which look something like this: (only there are a lot more values)

A B C
Band1 Song1 Band1
Band1 Song2 Band2
Band2 Song3 Band3
Band1 Song4
Band3 Song5
Band2 Song6
Band3 Song7

I need another column D with all the songs coresponding to a band; I want the sheet to look like this:

A B C D
Band1 Song1 Band1 Song1 Song2 song4
Band1 Song2 Band2 Song3 Song6
Band2 Song3 Band3 Song5 Song7
Band1 Song4
Band3 Song5
Band2 Song6
Band3 Song7

Is it possible to do this with a macro?:confused: Manual work would take a few days:mad:
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I want to take all the values from column C, one by one, find the match on column A and if it matches I need to copy the corresponding value from column B in another column D.
Anyone? Please help!
 
Upvote 0
‘Allo Scruff,
. Welcome to the board.
. You actually explained quite clearly, but Please try to learn to use the Forum Tools, at least using the Table Icon. - It is a pain in the ar## copying and splitting your data and putting it right in a Spreadsheet….

. Assuming that your Unique band ( Banned – get it? ) list is already in column C, then the VBA code is a Piece of Pi## and even I can do it… ( Modifying the code to make that unique Band list initially in column C is also quite easy.. )

…. Anyways
…..Before: (It is often with Excel / VBA things good to have a heading even if you don’t need it as it often simplifies things and occasionally is necessary.. )

Using Excel 2007
-
A
B
C
D
1
2
Band1NaughtySong1Band1
3
Band1NaughtySong2Band2
4
Band2NaughtySong3Band3
5
Band1NaughtySong4
6
Band3NaughtySong5
7
Band2NaughtySong6
8
Band3NaughtySong7
Scruff

…….After (It is often with Excel / VBA things good to have a heading even if you don’t need it as it often simplifies things and occasionally is necessary.. )


Using Excel 2007
-
A
B
C
D
1
BannedNauty SongsBanned UnicConcatenated Songs
2
Band1NaughtySong1Band1NaughtySong1 NaughtySong2 NaughtySong4
3
Band1NaughtySong2Band2NaughtySong3 NaughtySong6
4
Band2NaughtySong3Band3NaughtySong5 NaughtySong7
5
Band1NaughtySong4
6
Band3NaughtySong5
7
Band2NaughtySong6
8
Band3NaughtySong7
Scruff


( In the Spreadsheet the concatenated songs come out in a long row, like wot you wanted)


Code:

Code:
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] ScruffyNaughtySongs()
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("Scruff")
[color=blue]Dim[/color] runic [color=blue]As[/color] [color=blue]Long[/color], r [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] Long [color=lightgreen]'variables for "rows" or "vertical" count to be used in various loopings for row or first co ordinate in arrays'( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here)[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = ws.Cells.Find(what:="*", After:=ws.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/color]
[color=blue]Dim[/color] strSongs [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Temporary Variable for concantenated songs in each output row[/color]
[color=blue]Dim[/color] arrIn() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrIn() = ws.Range("A2:B" & lr & "").Value [color=lightgreen]'VBA lets you "Capture" the contents of a range to a Dynamic Array. Variant allows anything (within reason) to come in, but the Variant is also needed as it "sees" the Range "Object.. a funny thing - and a variant can be anything..[/color]
 
[color=lightgreen]'Make uniques Array[/color]
[color=blue]Dim[/color] lBand [color=blue]As[/color] Long: [color=blue]Let[/color] lBand = ws.Cells(Rows.Count, 3).End(xlUp).Row [color=lightgreen]'Last row with entry in unique bands list. Found by starting at last row in colzumn C, then going "Up" until something is found, then with .End returning a range from which the row property can be used to get the row number[/color]
[color=blue]Dim[/color] arrUnics() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrUnics() = ws.Range("C2:C" & lBand & "").Value [color=lightgreen]'Unique Array becomes a "2 dimensional 1 column" Arra for no particular reason - just mmakes it "appear" in the right orientaion in my head!![/color]
 
[color=lightgreen]'Main part, looping to make Array for Output[/color]
Dim arrOut() [color=blue]As[/color] String: [color=blue]ReDim[/color] arrOut(1 [color=blue]To[/color] lBand - 1, 1 [color=blue]To[/color] 1) [color=lightgreen]'Output Array to be filled in in a loop so it is non dynamic and we know it's size..[/color]
 
    [color=blue]For[/color] runic = 1 [color=blue]To[/color] lBand - 1 [color=blue]Step[/color] 1 [color=lightgreen]'Scruff: "take all the values from column C, one by one, "[/color]
        [color=blue]For[/color] rws = 1 [color=blue]To[/color] lr - 1 [color=lightgreen]'look down rows in sheet[/color]
            [color=blue]If[/color] arrIn(rws, 1) = arrUnics(runic, 1) [color=blue]Then[/color] [color=lightgreen]'Scruff: "if it matches I need to..."[/color]
            strSongs = strSongs & " " & arrIn(rws, 2) [color=lightgreen]'Scruff: "copy the corresponding value from column B in ......"[/color]
            [color=blue]Else[/color] [color=lightgreen]'No match do nothing. redundant code[/color]
            [color=blue]End[/color] [color=blue]If[/color]
       
        [color=blue]Next[/color] rws
    [color=blue]Let[/color] r = r + 1 [color=lightgreen]'"row" count for output Array[/color]
    [color=blue]Let[/color] arrOut(r, 1) = Trim(strSongs) [color=lightgreen]'Put concatenated songs in output Array for Column D ( Trim just takes off first space at start )[/color]
    [color=blue]Let[/color] strSongs = "" [color=lightgreen]'Empty tempory string for next looping of unic songs[/color]
    [color=blue]Next[/color] runic
 
[color=lightgreen]'Output to sheet[/color]
[color=blue]Let[/color] ws.Range("A1").Resize(1, 4).Value = Array("Banned", "Nauty Songs", "Banned Unic", "Concatenated Songs") [color=lightgreen]'A typical step that looks cleverer then it is, I resize first cell to a range including all headings I want, and then VBA lets me assign the values in a (Heasding here)  Array to the cells in a simplw = step[/color]
[color=blue]Let[/color] ws.Range("D2").Resize(UBound(arrOut(), 1), 1).Value = arrOut() [color=lightgreen]'Similar to the above just convenient to resize to size of Arrray i am actually outputing[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'ScruffyNaughtySongs()[/color]


. need more help, report back and I or someone will help ( me probably tomorrow..)
. Let me know Please anyways how you get on..

. Alan


P.s.
. I could do a Spreadsheet formula instead, or as alternative, if you like. Might be a bit messy if you have lots of bands and songs though
 
Upvote 0
wow!! thanks Alan!!
This works great!!
I tried to write my own code but it didn't work so well... I mean it didn't work at all.
What formula can be used to do this? I'm courious because I thought of using a formula but I couldn't find the right one. I'm so new to all of this.

Thank you again!!
 
Upvote 0
wow!! thanks Alan!!
This works great!!......!
yous wellcome. Thanks for the feeedback


What formula can be used to do this? I'm courious because I thought of using a formula but I couldn't find the right one. I'm so new to all of this.
................
Thank you again!!

. A formula would be one of those 'orrible 'CSE' things that no-one understands. I am getting close to understand them - but only because I 'ave a weird way of thinking....As long as you are careful how you put them in the spreadsheet then you don't have to know about them

... I could give it a go , maybe tomorrow, for a laugh...
.. It would be handy to know how many songs a band might have. That could make it a bit impractical if it is a lot.
.. I personally prefer macros, but I’m going through a weird phase of having a look at formulas just now, (If you look around Post #6 here
http://www.mrexcel.com/forum/excel-...function-pull-column-b-populate-column-c.html
or this thread
Row to column [SOLVED]
you see I have got it quite bad just at the moment… )


I will probably get over it. But for now I could / would maybe get one up and running for you...and maybe even explain to you how it works… wot few people can- even those that have a life time’s experience of coming up with brilliant formulas..

. So let me know about how many songs a band might have..
 
Upvote 0
A band might have up to 10 songs... but most of them have about 5.
It's the number of bands that scared me...
 
Upvote 0
A band might have up to 10 songs... but most of them have about 5.
It's the number of bands that scared me...


Hi,
You would have a few formulas probably looking a bit like the ones given right at the end of post # 6 here
http://www.mrexcel.com/forum/excel-...function-pull-column-b-populate-column-c.html
You would only have to paste them in once, in one row..But then you would need to “drag them down “ as long as your list is.. Makes a bit of a messy and very full with formulas spreadsheet..
. The code approach is much tidier....

Alan
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top