Concatenate strings using if statements within a while loop? Please help!

[car567]

Hi guys, I've been a lurker on the forums for a while now and I've gotten some great advice on some of my questions! Thanks for all the help! I now have a complicated situtation that I haven't been able to find the answer to. Apologies in advance for the long post; I’m just trying to include all the details.

Some background, I'm extremely new to coding in VBA, but I have a basic understanding of coding in Matlab. I'm using Windows 7, Excel 2010 (soon to be upgraded to 2013). I’ve already written the macro to filter my data, create several plots, wait for user input, and then assign an ID letter to each row based on several criteria (Sheet1), but now, I'm stuck.

I need a macro/VBA to automatically concatenate the strings in column C based on the ID in column D and then put that information info specific cells in a new sheet (Sheets 2 and 3).

Sheet1:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Critera1[/TD]
[TD]Criteria2[/TD]
[TD]Description[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Info1[/TD]
[TD]Data1[/TD]
[TD]String1[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Info2[/TD]
[TD]Data2[/TD]
[TD]String2[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Info3[/TD]
[TD]Data3[/TD]
[TD]String3[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Info4[/TD]
[TD]Data4[/TD]
[TD]String4[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Info5[/TD]
[TD]Data5[/TD]
[TD]String5[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Info6[/TD]
[TD]Data6[/TD]
[TD]String6[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Info7[/TD]
[TD]Data7[/TD]
[TD]String7[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Info8[/TD]
[TD]Data8[/TD]
[TD]String8[/TD]
[TD]Alpha[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2:
The concatenated strings (description) from Column C for each ID would be displayed in a single cell on separate lines (rather than on a single line separated by commas). The data for Alpha goes in C1, Beta in B2, Gamma in C3.

This sheet doesn't need headers

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]String2
String6
String8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD]String3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]String4
String7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet3:
The data for the rows assigned to ID Delta would be copied into a new sheet EXCEPT for column D (I don’t want the ID displayed on this sheet). This sheet needs the same headers as Sheet1.

I know I could do this by just copying everything from Sheet1 into Sheet3 and filtering Column D for ID Delta, but I’m looking for a more sophisticated way of doing this that would completely cut out everything unrelated to the data assigned to ID Delta

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Critera1[/TD]
[TD]Critera2[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Info1[/TD]
[TD]Data1[/TD]
[TD]String1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Info5[/TD]
[TD]Data5[/TD]
[TD]String5[/TD]
[/TR]
</tbody>[/TABLE]


I *think* the best way to go about doing this would be with If statements within a For or While loop. I didn't use the code tags since my attempt is incomplete (and probably wrong).

Dim i As Integer
i=1

Sheets("Sheet1").Select
Range("D2").Select

Do While Cells(i,1).Value <> ""​

If Cells(i,1).Value = "Alpha"​

Here is where we would concatenate the strings in Column C, maybe define a new variable and add to the end of the variable (varAlpha) for each time the loop finds a new Alpha? Then at the end of the loop, just put the value of that variable into the appropriate cell in sheet2?: (something like Sheets("Sheet2").Range("C1").Value = varAlpha)​

ElseIf Cells(i,1).Value = "Beta"​

Concatenate Column C strings for the rows associated with Beta​

ElseIf Cells(i.1).Value = "Gamma"​

Concatenate Column C strings for the rows associated with Gamma​

ElseIf Cells(i,1).Value = "Delta"​

Copy all data from the row associated with Delta into Sheet3.​

i = i +1​

Loop​

Any help you could provide, even if it’s only for part of this request would be most appreciated! Thanks! ray:

 

[Rick Rothstein]

You can use the code I posted in my mini-blog article here to do the concatenation part of your code...

<!-- title / author block -->LookUp Value and Concatenate All Found Results

 

[car567]

Thanks Rick. I skimmed through the thread you linked and that looks helpful! I'll read through it more closely and try the code.

Can I just paste the UDF code directly into the same macro that I've created to do everything else? I have "Sub Macro1()" in "Module 1", so do I put "Function LookUpConCat" within in my Sub Macro1() or outside of that macro but still within Module 1?

 

[Rick Rothstein]

Thanks Rick. I skimmed through the thread you linked and that looks helpful! I'll read through it more closely and try the code.

Can I just paste the UDF code directly into the same macro that I've created to do everything else? I have "Sub Macro1()" in "Module 1", so do I put "Function LookUpConCat" within in my Sub Macro1() or outside of that macro but still within Module 1?

You would copy the code from my article into Module1, but not within your code. You would use the LookUpConcat function (with the appropriate arguments) directly within your code wherever you want the list produced by the function to go.

 

[car567]

Thank you for your help Rick! It took some time, but I was able to implement your concatenate code in my macro, and it works beautifully now. I was also able to to figure out how to write the If/else loop, so my entire question is resolved now!

 

[Rick Rothstein]

Thank you for your help Rick! It took some time, but I was able to implement your concatenate code in my macro, and it works beautifully now. I was also able to to figure out how to write the If/else loop, so my entire question is resolved now!

Great! I am glad you were able to work everything out.