delete washout transactions

[Peishi]

I have a spreadsheet that contains more than 30,000 transactions. There are a lot of wash-out transactions in the spreadsheet. The wash-out transactions are the ones that the AP department accrues and reverses and they are with the same absolute value on the same transaction date. I used absolute and sort functions to identify the wash-out transactions; however, it was very time-consuming to delete those transactions one by one manually. Is there any way to do it efficiently?

 

[stanleydgromjr]

Peishi,

Welcome to the MrExcel board.

What column is being used as the indicator for the "wash-out transactions"?

And, what phrase is being used in that column to indicate the "wash-out transactions"?

What is the start row of your data?


Have a great day,
Stan

 

[Peishi]

The worksheet contains Source Type (column A), date (column B), and transaction amount (column C) with more than 33000 rows. My objective is to delete the transactions that have corresponding negative transaction values with the same dates or the source type indicates AP Accr or Rev AP Accr for the corresponding transactions. For example:

C1R1 = "Source Type", C2R1 = "date", C3R1= Amount
C1R2="AP Accr", C2R2= "3/1/2008", C3R2= $100
C1R3="Rev AP Accr ", C2R3= "3/1/2008", C3R3= -$100
C1R4=" AP Accr", C2R4= "3/10/2008", C3R4= $300
C1R5=" Rev AP Accr ", C2R5= "3/11/2008", C3R5= -$300

I read the thread http://www.mrexcel.com/forum/showthread.php?t=55965 and thought it was the closest I could find for my problem; however, I couldn't quite understand the solution formula; i.e. {=IF(COUNTIF($A$1:$A$10,A1)>1,IF(SUM(($C$1:$C$10=-C1)*($A$1:$A$10=A1))>0,"Delete",""),"")}
(array entered - CTRL + SHIFT +ENTER)

Later, I finally figured out the array does not work well with condition IF(AND), or, IF(OR), so the best way to address the condition is to use * for AND condition, + for the OR condition. But I still don't quite understand why use COUNTIF.

I thought D2= {MAX (C2=-data_range)*(B2=date_range) *(row=data_range) }(array entered - CTRL + SHIFT +ENTER)
will show me either 0 or the row # if there is a corresponding row (the negative value) given that the data_range is the column for the "amount" and the date_range is named for the "date"column. Then I can sort the data. The return value 0 will be the non-wash-out transactions. The non-0 return value will be part of the wash-out transactions.

But I don't know how to to define the washouts for all three conditions : the transations must have a corresponding negative amount with the same date or the source type indicates AP Accr or Rev AP Accr since the corresponding washout transactions might be either on the same date or Rev AP Accr not done on the same date.

Are there any better way to solve the problem?

I am new to this site and excel. How do I make a table to demonstrate part of the the spreadsheet if I am not clear enough for my questions?

 

[stanleydgromjr]

Peishi,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356


Or, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Peishi]

The instruction stated:

1. Click the "profile" link--which is in the group of text links at the top-right of every page of the board.
2. In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.
3. Click the "Submit" button to save any changes to your profile

I cannot find the profile link with the list of preferences.
I skipped thosew part and tried to follow the rest of the directions. I was unable to post the table. It only had the source codes w/o table.

 

[stanleydgromjr]

Peishi,

I skipped thosew part and tried to follow the rest of the directions. I was unable to post the table. It only had the source codes w/o table.

Yes, this is correct.

Then click on the "Submit Reply" button. The newly posted information will show correctly.


Have a great day,
Stan