List cell values seperated by comma if criteria met

[Mike Pearson]

I created a sheet as below:

A B
1 Company Name When Approved
2 ABC Company 6/1/07
3 XyZ Inc 5/1/07
4 aaa LLC 6/2/07
5 aba Company 4/20/07

There's a lot of other info but these are the columns i want to use for this
formula.

Anyway what I want to happen is if month(A2:A5) = month(today()) then I want
it to list the name of those companies on sheet2 a1 with each name seperated
by a comma.

Do I need to make a macro to do that or is there a formula that can do this?

Even if it has to list them down a column that would be fine as long as
there are no gaps in between. I figured out a way to do it but it just
leaves a blank cell if false, so it may have one company name and then 20
blank rows and then another company name???

Any ideas?

 

[T. Valko]

2 solutions at your other post

Biff

 

[Gary''s Student]

How about:

Sub mike()
Dim d As Date
Dim s As String
s = ""
m = Month(Now())
n = Cells(Rows.Count, "B").End(xlUp).Row
first = True
For i = 2 To n
m1 = Month(Cells(i, "B").Value)
If m1 = m Then
If first Then
first = False
s = Cells(i, "A").Value
Else
s = s & "," & Cells(i, "A").Value
End If
End If
Next
Sheets("Sheet2").Range("A1").Value = s
End Sub