[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]