On Sheet1 I have a column of account names and a column of dates of last vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to month(today())
then I want it to list all of those accounts seperated by a comma on sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

As an alternative, perhaps one formulas play
which outputs the required list into a col in Sheet2?

Source data assumed in Sheet1 cols A and B, from row1 down,
with col A = companies, col B = dates (real dates assumed)

In Sheet2,

Put in A1:
=IF(Sheet1!B1="","",IF(MONTH(Sheet1!B1)=MONTH(TODA Y()),ROW(),""))

Put in B1:
=IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW())))
Select A1:B1, copy down to cover the max expected extent of data in Sheet1,
say down to B100. Hide away col A. Col B will return the required list of
companies, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike Pearson" wrote:
On Sheet1 I have a column of account names and a column of dates of last vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to month(today())
then I want it to list all of those accounts seperated by a comma on sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

This should do it
Sub getmonthlydata()
For Each c In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
If Month(c) = Month(Date) Then
'MsgBox c.Address
ms = ms & ", " & c.Offset(, -1)
End If
Next c
'MsgBox Right(ms, Len(ms) - 1)
Sheets("sheet2").Range("d7") = Right(ms, Len(ms) - 1)
End Sub

--
Don Guillett
SalesAid Software

"Mike Pearson" <Mike wrote in message
...
On Sheet1 I have a column of account names and a column of dates of last
vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to month(today())
then I want it to list all of those accounts seperated by a comma on
sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

This looks like a great way to do it, I am just a little new at writing
macros. Would you mind please being a little more descriptive so that I can
figure out how to put this thing in. I really appreciate it.

"Don Guillett" wrote:

This should do it
Sub getmonthlydata()
For Each c In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
If Month(c) = Month(Date) Then
'MsgBox c.Address
ms = ms & ", " & c.Offset(, -1)
End If
Next c
'MsgBox Right(ms, Len(ms) - 1)
Sheets("sheet2").Range("d7") = Right(ms, Len(ms) - 1)
End Sub

--
Don Guillett
SalesAid Software

"Mike Pearson" <Mike wrote in message
...
On Sheet1 I have a column of account names and a column of dates of last
vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to month(today())
then I want it to list all of those accounts seperated by a comma on
sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Don Guillett
SalesAid Software

"Mike Pearson" wrote in message
...
This looks like a great way to do it, I am just a little new at writing
macros. Would you mind please being a little more descriptive so that I
can
figure out how to put this thing in. I really appreciate it.

"Don Guillett" wrote:

This should do it
Sub getmonthlydata()
For Each c In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
If Month(c) = Month(Date) Then
'MsgBox c.Address
ms = ms & ", " & c.Offset(, -1)
End If
Next c
'MsgBox Right(ms, Len(ms) - 1)
Sheets("sheet2").Range("d7") = Right(ms, Len(ms) - 1)
End Sub

--
Don Guillett
SalesAid Software

"Mike Pearson" <Mike wrote in message
...
On Sheet1 I have a column of account names and a column of dates of
last
vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to
month(today())
then I want it to list all of those accounts seperated by a comma on
sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?

Thank you

"Don Guillett" wrote:


http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Don Guillett
SalesAid Software

"Mike Pearson" wrote in message
...
This looks like a great way to do it, I am just a little new at writing
macros. Would you mind please being a little more descriptive so that I
can
figure out how to put this thing in. I really appreciate it.

"Don Guillett" wrote:

This should do it
Sub getmonthlydata()
For Each c In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
If Month(c) = Month(Date) Then
'MsgBox c.Address
ms = ms & ", " & c.Offset(, -1)
End If
Next c
'MsgBox Right(ms, Len(ms) - 1)
Sheets("sheet2").Range("d7") = Right(ms, Len(ms) - 1)
End Sub

--
Don Guillett
SalesAid Software

"Mike Pearson" <Mike wrote in message
...
On Sheet1 I have a column of account names and a column of dates of
last
vist
ie:

abc company 6/1/2007
xwz corp 4/15/2007
exl inc. 2/12/2007
aaa corp 6/5/2007
bbb inc. 6/11/2007

What I want to do is if the date of last visit is equal to
month(today())
then I want it to list all of those accounts seperated by a comma on
sheet2.
ie:

abc company, aaa corp, bbb inc.

Is that possible, if so how?