Clearing Account formula ?

Coach Andrew said:

Can you help explain your objective?

Click to expand...

Dead simple: for each row I look in the same table for an exact match with:

1. the negative version of AMOUNT (HOME) column ('negative version' actually means the reversed sign, so +ve it was -ve and vice versa)
2. the VENDOR column
3. the BATCHNO column

I then put the index numbers (from the Index column) of the rows found into the Possibles column, then it's over to you to use them to examine the those rows to see if they should be paired.
Any row with nothing in the Possibles column means no match has been found at all (which means there are no matches on those 3 columns, so no need to look for any).

Ok, sounds good. Can you send me the revised file with these latest changes? I can try it out on current Sept data.

Coach Andrew said:

Ok, sounds good. Can you send me the revised file with these latest changes? I can try it out on current Sept data.

Click to expand...

The file attached to msg#19 is that file which does as described in msg#21. (It just matches one more thing than the file in msg#9.)
As I said earlier, for more exact/likely matches:

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.

Click to expand...

Added more data to file

Added more data to file, refer to Sep data file.

In the attached, I've left it with a single cell selected (AC2514) which is showing 4 possible matches on just the 3 column matches as described in msg#21.
I've autofiltered the result table not to show blanks in Possibles column.
I've highlighted those possibly matching rows.
There are similar rows with multiple possible matches elsewhere.
To move forward, I need you to look at the rows and tell me if there's any info in any of the rows (you'll have to unhide columns) that can be used to eliminate possible matches.
That done, I can try to translate what you suggest into code so it's done automatically.

I can't attach the file here (too big) so here's a link to it: https://app.box.com/s/95ag50tg5za2ewa6x1o50w4tkch4b66n

Ok Thanks, I will have a look into the revised file and let you know.

Hi, was hoping to use PQ for my Oct data. I'm fairly new to PQ so I'm not quite sure how I connect my new data to this file. Any suggestions?

Quick and dirty solution: cut and paste the data into the table in the file I linked to in msg#25.

Another solution: In the attached, cell A1 contains a path to the file (workbook). Edit this to point to your new file, then refresh the query at cell C1.
Cell A1 is actually a named range: FileName. There's a little macro that's called if you double-click on that cell which will let you pick the file (saves mis-typing the path and filename). You still need to refresh the query after doing this.

It's a bit slow at the moment, I haven't sussed out how to speed it up (it should be a lot faster).

Separately, aren't you likely to want multiple months' files brought in since I'd expect some transactions to be reversed in a different month from their creation(?). What can be done is for Power Query to pick up all files in a folder (or a subset of them) so that all you'd need to do is to make sure the files you want processing are all in the same folder.

Thanks, I will have a try and let you know if I have any issues.

An error occurred in the ‘fnPossibles’ query. Expression.Error: We cannot apply field access to the type Null.
Details:
Value=
Key=Index


Got this error message?

1. Which method are you trying? Quick and dirty, or the last file i attached? (It may not matter.)
2. Can you put together some data so that I can see this happening here?

I'm trying to link to my excel file. Attached is the test data for Oct

I downloaded your file and saved it. With the file I attached in msg#28, I updated cell A1 (with a double-click), then refreshed the table. No error, got the table as in attached (table left filtered for no blanks in Possibles column.
…still investigating it here.
Can you try again?