Generate Payment Statement
 

I'm hoping I can do this with functions but it may need to be a macro.
I've been looking at it for awhile now and can't work it out.

I have two columns. Column A has the dates of each day, Column B has a
figure next to the date that an amount was paid. Some days have nothing
in column B.

A.........B
1/1/05 10
2/1/05
3/1/05 5
4/1/05 5
5/1/05

I have to produce a statement of payments received. Therefore I want a
listing of the date with the amount received to be generated
automatically.

from above:
1/1/05 10
3/1/05 5
4/1/05 5

Any help would be very much appreciated.

thanks

adrian


--
myty
------------------------------------------------------------------------
myty's Profile: http://www.excelforum.com/member.php...o&userid=24592
View this thread: http://www.excelforum.com/showthread...hreadid=381844

select row 1 go to, data-auto filter

go to payment, drop down box select "non blanks"


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381844

Thanks for the reply.

This method still involves copying and pasting to other cells. I was
hoping for a solution that filled the other cells automatically.


--
myty
------------------------------------------------------------------------
myty's Profile: http://www.excelforum.com/member.php...o&userid=24592
View this thread: http://www.excelforum.com/showthread...hreadid=381844

backup you workbook before trying this macro

This will copy cells with payment values in column e


Sub macro()
Dim i, c, c1 As Variant
i = 0
c = 2
c1 = 1
While i = 0
If (Range("a" & c).Value = "") Then
i = 1
Else
If Range("b" & c).Value = "" Then
Else
If (c1 = 1) Then
Range("a1:b1").Select
Selection.Copy
Range("e" & c1).Select
ActiveSheet.Paste
c1 = c1 + 1
End If
Range("a" & c & ":" & "b" & c).Select
Selection.Copy
Range("e" & c1).Select
ActiveSheet.Paste

c1 = c1 + 1
End If
End If
c = c + 1
Wend
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381844

Back up your workbook before trying this macro
simpler version of macro, using auto filter, it will copy to column e


Sub Macro10()
Rows("1:1").Select
Selection.AutoFilter
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="<"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("e").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381844

Great, thanks for that. I'll play with it a bit but should do the
trick.


--
myty
------------------------------------------------------------------------
myty's Profile: http://www.excelforum.com/member.php...o&userid=24592
View this thread: http://www.excelforum.com/showthread...hreadid=381844