copy and paste question

[paulked]

It's Dave's code that changes the currency, I'll have a look at that tomorrow.

Yes, the 'circle of patience' is annoying but, alas, cannot be gotten rid of. There may be a quicker alternative to solve the number crunching, but struggling with time to look into it.

[austenr]

ok, no worries. this is awesome. formatting change and were done. as a side note, wife and i always wanted to visit your country. looks very beautiful. perhaps some day.

[paulked]

I found a minute to make the changes... attached.

Wales, especially North Wales, is a beautiful country and you would be more than welcome here. It's like a smaller version of Maine in a way, but without the Clams (we do have fantastic Lobsters though!)

[paulked]

I've just noticed that the last sample csv file you gave me didn't have invoice numbers allocated to some of the transactions. To make sure that all the unreconciled transactions are picked up, change the the 1 to a 3 (in red) in the routine below.

Sub MatchCheque()
    Dim ar, i As Long, rw As Long, lr As Long, y As Long
    lr = Cells(Rows.Count, 3).End(xlUp).Row
    rw = 5
    Range("F5:H" & Rows.Count).ClearContents
    Range("A5:C" & Rows.Count).Interior.Color = xlNone
    For i = 5 To lr
        If Cells(i, 3) = Range("f3") Then
            Cells(rw, 6) = Cells(i, 1)
            Cells(rw, 7) = Cells(i, 2)
            Cells(rw, 8) = Cells(i, 3)
            rw = rw + 1
            Range("A" & i & ":D" & i).Select
            Selection.Interior.ColorIndex = 44
            Cells(i, 4) = "X"
            y = 1
        End If
    Next
    If y = 0 Then
        If MsgBox("Not found as a single transaction, do you want to try Dave's multiple function?", vbYesNo, "Sorry...") <> vbYes Then Exit Sub
        Dave
    End If
    Application.EnableEvents = True
End Sub

Alternatively, when loading the CSV data, if the Invoice # is blank then load the Payment #

[austenr]

Thank you very much my friend. This is more than I had hoped for. Satay safe and thanks again. If I run into problems which I dont expect to I will let you know.

[paulked]

You are very welcome, take care

[austenr]

Good Day Paul

Im running into a problem. In the attached workbook, the bottom 2 invoices do add up to what is in the search box but when I run it I get no matches. Any thoughts as to why?

[paulked]

It worked 1st time for me, but I think I know where the problem lies. Dave's solution, although very good, is not that reliable. Firstly It uses random numbers, for reasons I can't fathom, and is prone to 'locking up' after a few uses for no apparent reason.

I'm working on it at the moment but, for the interim, try closing (saving on exit) and re-opening and try again. That has worked for me!

[austenr]

very good. thanks

[paulked]

After a few hours of hard slog I think we're there, but there is bound to be a bug or two in 'there'!

I've made a few changes:

1. The search for multiple matches is now done using a routine which checks every possibility. The problem to solve the matching is generally known as the 'subset sum' and takes longer and longer the more items that are in the list. It also takes loads more time to deal with negative figures, as your credits and payments are!
2. Once a match is made, use the 'List Reconciled Invoices' button with the 'Delete from Summary' checkbox checked. This will make the searches much, much faster.
3. Max Search Time: to save you waiting an hour or two with complex searches, the search will time-out after this period.

I understand that it may be difficult but try and get rid of the negative numbers first, it will make a huge difference in time waiting!

Try it and please let me know how you get on.

Cheers

[austenr]

thanks a bunch. a couple questions. after the matches are made if any i see that there are invoices left in the import column and some populate in the orange section. whats going on there?

[paulked]

Not sure I understand. This is a typical sequence:

Start with new csv file (top left pic)
Enter check amount (simulated in top right pic)
After enter the matches are highlighted under the csv columns and listed under the check columns (bottom left pic)
List Button pressed and the highlighted invoices are moved to the Reconciled columns.

[paulked]

Ah, if you mean under the 'Check' columns they will disappear on the next search. They are left as a reminder of what was searched on, if that makes sense?

[austenr]

clarification. after you put the search number in and click out of the cell what are the invoices that populate in the orange (middle section) and I assume what is left in the original load column (green) are the ones that didnt match after you click List reconciled invoices. Been testing it out and its super quick and again is more than i could ever imagine it being. thank you again/. btw, where did you learn how to do this so well?

[paulked]

Ok. I entered an amount of 121.68 and this is what comes up:

67175b.jpg

Then I hit the 'List Reconciled' button and:

67175c.jpg

The 'purple' ones in the middle are just the search results from the last match.

[paulked]

If you have a long list on either side they act as a 'reminder' of what you last reconciled. We can do away with them if you wish, not a problem.

[austenr]

ok the orange ones were throwing me. were good i think. thanks again. stay safe.

[paulked]

Any problems, post back here

[paulked]

I'm like a dog with a bone here, unwilling to let go!

I've noticed that this struggles with negative numbers, as did Dave's code. But I guess you never get a check for a negative amount!

The other thing is that on the last csv I saw (InvoiceDetail.csv) there were a number of negative and small figures. These raise the chances of multiple combinations giving the same answer. In the latest update it looks for multiple combinations (up to three) and if there are more than one, gives you a choice of which you want to pick.

Other than the above, it all seems ok!

In answer to your earlier question, I learnt VBA, or more accurately am still learning, from forums like this

Best regards

[austenr]

Hi Paul,

Been playing around with this more. Seems that the more the number of invoices the longer it runs and eventually locks up. Have to kill it with the task manager. This particular batch has 30 invoices and 2 negatives one being the pmt. Does it seem reasonable to you?