Clearing Account formula ?

C

Coach Andrew

New member
Joined
Jun 24, 2021
Messages
20
Reaction score
0
Points
0
Location
Ottawa
Excel Version(s)
Excel 2016
Trying to figure out a formula to help reconcile clearing account with over 5K lines of data each month.
Currently it is a manual process of using ABS formula and colour coding entries that offset, example $100 with -$100 = Zero.
It takes about 4 hours to be this each month and was thinking there must be a better way.

Any ideas?
 
Sample Clearing account data

Hi,

Here is attached excel file sample data I used for May clearing, I tried a few formula's but without success.
Let me know if you have any ideas or want to discuss process in more detail.

Thanks

Andrew
 
I wasn't sure if attached or not. Is there a trick to it?
 
Sample Clearing account data

File attached
 

Attachments

  • Visa Clearing CAD - May 2021 Sample Excel Guru.xlsx
    474.8 KB · Views: 14
Hello, Still trying to find a way to automate clearing account. Anyone have any good ideas, would use of Power Query help?
 
See table at Column M of the attached and tell me if you can do anything with it?
See columns X & Y
Assumes amounts and vendor name should match.
 

Attachments

  • ExcelGuru11223Visa Clearing CAD - May 2021 Sample Excel Guru.xlsx
    465.3 KB · Views: 5
Hello, I see the results if Y is blank then not cleared. The others have possibilities, not sure understand what the PQ is doing. There also appears to be errors in it. Can you explain further?
 
Coach Andrew said:
Hello, I see the results if Y is blank then not cleared.
Click to expand...
Correct, it couldn't find a match for the Vendor/Amt combination.
Coach Andrew said:
The others have possibilities, not sure understand what the PQ is doing. There also appears to be errors in it. Can you explain further?
Click to expand...
Could you point out the errors?
There's little point in explaining further if I've got it wrong.
 
B9EyajcWPjPJAAAAAElFTkSuQmCC
 
B9EyajcWPjPJAAAAAElFTkSuQmCC
Error message An error occurred in the ‘Table1’ query. Expression.Error: 5 arguments were passed to function which expects between 2 and 4.
 
What version of Excel are you using?
Is there any clue as to which function or which line the error's occuring on?

Edit: There's only one such function with 5 arguments in the Table1 query, so remove , Int64.Type the from:
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
to leave:
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
I think you have an older version.

ps msg#12 is completely blank here.
 
Last edited:
So are there any other errors in the results table?
There might be a possiblity of refining the possibles column.
Maybe by using the date?
Is a negative number the clearing of an amount or is it the positive? (Or is it not possible to tell?)
Is a clearance date always greater or equal to the the non-cleared date? We might be able to eliminate a possible clearance row due to it being earlier than the uncleared row you're trying to match it with.
Are there any other columns that could be brought into play; Batch no has to be greater/smaller?, SACE CODE?
 
Last edited:
I don't see any other errors. On the date, most times the 1st transaction is a positive, then it gets reversed out with a negative. Normally it is the latest transaction date that we want to show as outstanding. example $100 May 31, -$100 June 1, then $100 June 30. Also when an entry reverses out it has the same batch no. Hope this helps, appreciate your efforts on this.
 
I won't be able to narrow things down with positive/negative since you only say most times. It's a shame because I wouldn't have bothered looking for matches of negative amounts at all, but these will continue to show.
It might be the case that a transaction showing possibles, than none of the possibles pertain, which would mean that transaction hasn't been cleared. This means that the presence of possibles doesn't automatically mean it's been cleared.

Matching the batch numbers as well reduces the possibilities to only 1 in the example data you supplied. See attached. Is it valid to do this?

I'll have a play and see what I can do later, but only if I can have a more extensive sample data to play with.
In the meantime can you confirm the following:

  • A transaction can never be reversed out earlier than it was created.
 

Attachments

  • ExcelGuru11223Visa Clearing CAD - May 2021 Sample Excel Guru_v02.xlsx
    465.1 KB · Views: 5
Correct, transaction can never be reversed before being created. Also the latest file sent has less items indicating cleared, as the 1st 2 transaction would net off and therefore not be in the final list. Can you help explain your objective? I know the manual process I'm using is time consuming so any improvement to narrow down items that have not cleared is useful.
 
You must log in or register to reply here.
Back
Top