Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Removing bad payments from report

Status
Not open for further replies.

Biznez

Technical User
Apr 9, 2015
106
CA
Hi , i have payments in report that went nsf and i would like to remove those 2 rows. For example

Code:
A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00[/color]
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00-


The red are the rows i want removed using vba code
Thanks
 
Hi,

I could derive what I think is the logic based on your example, but it would be much more desirable if you were to explicitly state the logic clearly, concisely and completely.
 
Hey Skip, ok so we are in sheet "Input"
Column A starting at A5 has the Value date (cust payment date), in column B starting at B5 is the payment type meaning cash or pre-auth etc...and in column C starting at C5 is the amount cust paid. Sometimes when a payment is made that payment goes nsf. These are the rows i want removed.

Code:
A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
3/31/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	NF     	    487.00
5/12/2008	CS     	    974.00-[/color]
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
[COLOR=#EF2929]09/01/11        PA          36.24- 
09/01/11        NF          36.24[/color]

so i want the rows in red removed because those are bad payments
 
sorry, correct red row removed are these ones not the one above

Code:
A              B                   C
Date         Type               Amount
1/31/2008	PA     	    487.00-
2/29/2008	PA     	    487.00-
[COLOR=#EF2929]3/31/2008	PA     	    487.00-
3/31/2008	NF     	    487.00[/color]
5/12/2008	CS     	    974.00-
5/31/2008	PA     	    487.00-
6/30/2008	PA     	    487.00- 
[COLOR=#EF2929]09/01/11        PA          36.24- 
09/01/11        NF          36.24[/color]
 
i think the best way to do this is if in column C starting at C5 finds the negative sign "-" and one cell below has no "-" sign, those 2 rows can be deleted.

 
I reall don't believe that you have stated the logic completely.

Think it through again and make a clear, concise and complete logic statement.
 
ok let me try again. I already extracted data from mainframe and now we are working in excel sheet "Input" only.

If in column C there is a character "-" and one cell below has no character "-" then delete those 2 rows


 
So the date is irrelevant. It mattered not that the dates are identical or not?

BTW, this is a forum707 question.

So the first thing that you ought to have done is to change the TEXT on the amount column, that you could not possibly use in ang arithmetic operation, to NUMBERS.

You might use a formula like this...

=IF(RIGHT(C5,1)="-",-1,1)*LEFT(C5,LEN(C5)-1)
 
Hi Skip, yes dates are always identical for bad payments but the problem there would be if the customer made 2 payments on the same day those will also get deleted. so thats find a character would work. Any suggestions?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top