List cell values seperated by comma if criteria met
 

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?

List cell values seperated by comma if criteria met
 

2 solutions at your other post

Biff

"Mike Pearson" wrote in message
...
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?

List cell values seperated by comma if criteria met
 

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

--
Gary''s Student - gsnu200726