Concatenate Data Based on Conditional Statement

[Msullivan]

Hi,

What I'm trying to do is take a list of objects that have a corresponding number and place them in a 3x3 table in Excel. Each object is assigned a number 1-9, and will be placed in the table accordingly.

For example:
Col A ColB
Ball 9
Bat 9
Glove 8
Field 9
Hat 1

From here in my table I want to concatenate all the data from Col A, in lets say Cell E1, if ColB is a 9. There could be 3 9s or there could be 15. Can anyone help, or is there a better way to be able to organize text in a grid?

Thanks,
Mike

 

[CharlesChuckieCharles]

=A1 & B1 or =A1 & " " & B1

=IF(B1=9,A1&B1,"")
=IF(B1=9,A1 & " " & B1,"")

 

[Msullivan]

Thanks for the response, but I don't think that'll work b/c I could have anywhere from one 9 in my list of objects to 15 or 20. I believe I need some kind of looping code for this to work properly.

 

[CharlesChuckieCharles]

I don't think i understand ... the example wasn't an example?

 

[Msullivan]

The example was a small subset of the data I'll be evaluating. So I want to concatenate the data from Col A, if Col B is a 9. And there isn't a limit on the amount of 9s there could be in the data set. Make more sense??

 

[Msullivan]

I was able to come up with a solution. Here it is for anyone that is curious...

Sub Populate(X As String, Y As Integer, Z As String)
Application.ScreenUpdating = False
Counter = 2
Worksheets("Summary").Range(Z).ClearContents
Do
If Worksheets(X).Range("P" & Counter) = Y Then
Worksheets("Summary").Range(Z) = Range(Z) & " / " & Worksheets(X).Range("B" & Counter)
End If
Counter = Counter + 1
Loop Until Worksheets(X).Range("P" & Counter) = ""
Application.ScreenUpdating = True
End Sub