copy and paste question

[austenr]

I need to copy and paste what is in column A and column C only if there is an X next to the amount in column D. I am trying to figure out some sort of loop using Range.Offset but I am not sure how to copy and paste those columns only. Perhaps there is a better way but cant think of one. Sample workbook attached. Pasting should begin in E2.

[paulked]

Something like this?

[austenr]

this is what i came up with sort of but the whole row is selected and I only want the first three cells copied and pasted.


Sub Copy_and_Paste()a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a


If Worksheets("Sheet1").Cells(i, 4).Value = "X" Then
Worksheets("Sheet1").Rows(i).Copy
Cells(1, 3).Offset(0, 6).Select




End If
Next i
End Sub

then find the next row and paste to the new location under the first one.

[austenr]

dead on paulked thank you.

[paulked]

First 3 cells or Columns A & C as in the original post?

[paulked]

This does the first 3 columns

[paulked]

You're welcome, stay safe :thumb

[austenr]

you too. this is serious stuff. i fear our hair may look like your avatar before its all over. : pray2:

[paulked]

:rofl: Mine is not far off now, and about the same colour with the worry :nervous:

[austenr]

Hello Paul,

I need to take this a bit further so was hoping you could help or if someone else wants to jump in thats fine too.

Im posting two workbooks: Book 1 test matches and Sample Data which is a csv file .

Book 1 Test Matches works fine as it is but now I need to modify it and add a few things.

From the Sample Data workbook I need to capture the invoice number, transaction date and amount and put them in the columns under results when a match is found that make up the total given. Currently the user has to copy from the sample data workbook the amount in the last column and paste it into the Book ! test workbook column A then run the macro.

I suppose there is no easy way around getting the invoice number and transaction date other than having the user copy and paste them but that seems risky as they could potentially paste the wrong data in the wrong columns. So my thought was to take the whole worksheet that is the Sample Data (which is a csv download from our homegrown system which the user exports from the account they are on) and paste it somewhere on the Book 1 Test sheet 1 and find a way to pull the information from that.

I know the existing macro will need to be enhanced and I dont have that skill level having not written VBA for quite a few years. Ive been doing mostly SQL Server and BI for quite a while. This was kind of pushed on me but I dont have the skill to turn it around that quickly.

In a nutshell thats what I need to accomplish.

[paulked]

Hi.

The updated file attached will pull data from the CSV file when the number in C2 matches the number in column M of the CSV file (-300 is not the same as 300!).

I'm not sure this is what you want though.

It would be helpful if you could post the workbook which contains the existing code and, if different from the sample file, a copy of a 'real life' csv file.

[austenr]

hi,

thats a good solution. a couple of questions.

assume both workbooks need to be open and in the same directory, correct?
you added a button which i assume pulls the invoice number and date from the csv file thats not a problem.

most likely the csv will always go to the downloads folder and there will most assuredly be more than one csv in it so was wondering how to get around that so the most recent is chosen.

i know the user will want the non csv workbook with the macro that does the work finding the matches on their desktop so thats why i asked the same directory question.

As for the original code that does the matching it is in the non csv workbook i posted in sheet 1 but i assume it could be put into a module.

The csv file is one i pulled today so its live data.

I like what you did a lot. just have concerns on how you ran it? Were both workbooks open and in the same folder?
To get the data under results I am assuming I have to use the button you created.

[paulked]

I asked for the workbook so I could get a grip on what you are trying to do overall.

I assumed both work books were open, that way it does not matter what directory they are in.

When a CSV is updated, does it have the same name? Does go into the same folder (directory)?

If you can give me an idea of the work-flow or what the user needs to do eg

User opens 'Daily Checks' workbook, searches for all unpaid invoices, Prints out the list. Needs to search for a queried invoice, print details etc etc.

I can then probably provide the best solution for you. eg, it may be better to use a UserForm, so the end user sees only what is needed = less prone to errors.

[austenr]

Heres the workflow:

1. Employee opens the 'Daily Checks' workbook because she/he has a check to process that did not come with instructions as to where to apply the amount. Say the check is for 276.32 and the copy of the check doesnt have the instructions on how to distribute so we only have 1 lump sum to deal with.

2. Employee opens the account on the system that has an option to export all unpaid invoices into a csv file. The file name changes every time but as of now they are by default going to the downloads folder.

3. Right now employee has the 'Daily Checks' workbook open on the desktop as well as the csv file they just downloaded. So both workbooks open.

4. Employee copies and pastes the column of currency we are using in the 'Daily Checks" workbook from the csv fil, pastes it into col A2 of sheet1 then clicks the Find possible matches button.

5. Macro runs trying to find combinations of invoices if they exist that match the total of 276.32 and if they exist puts an X next to each amount.

Thats the workflow as it stands.

What I need to do is take it a few steps further by listing the matched amounts with the invoice number and date and matching amount from the csv file under the results on the 'Daily Checks' workbook.

At this point they have a matching situation then the check is posted against those invoices.

This whole thing is trying to make your best guess as to where the money gets distributed to the invoices. Also If no matches are found that add up to that total there should be a message stating "No matching invoices found".

The solution being used may not be the best solution but its what I have at the time.

Hope this answers your workflow concerns. Please post back with any questions or you need more clarification. As always thanks in advance for your help. Much appreciated.

[paulked]

Thanks, I'll sleep on that (it's gone midnight here)) to get my head round conciling the different values to match the payment. Can I assume that if payment is for more than one invoice then the Merc # will be the same? Any filters will help :yes

[austenr]

Heres the code that does the matching. Shout out to Dave from here that wrote it. As for more than one invoice being split dont worry about that. The below as you have most likely seen already just applies it 1 for 1. Also not sure but if there are several invoices that are for $10 and the code decides to use it I suppose that it is randomly chosen. We just have such a backlog of unapplied payments thats what were trying to accomplish. Post the money to get it off the ledger. Its likely the customer may see the next bill and call and tell us to apply differently which is ok. BTW post 11 of this thread would be fine if the csv were consistently named. Your example its very close to a solution that is workable. It might be worthwhile to try that path as the code works most of the time and your 1 invoice is the result I would be happy with. Anyway heres the code that does the heavy lifting:


Public Function CheckCheques2(InCol As String, OutCol As String, InRng As Range) As Boolean Dim LastRow As Integer, LoopCnt As Double, RowNum As Integer, TotNum As Double, Cnt As Integer
Dim Arr() As Variant, ArCnt As Integer, LetterArr() As Variant, LetCnt As Integer
LetterArr = Array("X", "Y", "Z")
Randomize
With Sheets("Sheet1")
.Columns(1).Style = "Currency"
LastRow = .Range(InCol & .Rows.Count).End(xlUp).Row
.Range(OutCol & "2:" & OutCol & LastRow).Clear
End With
LetCnt = 0
ArCnt = 0
above:
LoopCnt = LoopCnt + 1
'change iterations to suit
If LoopCnt = 1000 Or LetCnt = 3 Then
Exit Function
End If
getnewrow:
RowNum = Int((LastRow * Rnd) + 1)
If RowNum <> 1 Then
If ArCnt <> 0 Then
For Cnt = LBound(Arr) To UBound(Arr)
If Arr(Cnt) = RowNum Then
GoTo above
End If
Next Cnt
End If
'exclude blank cells
If Sheets("Sheet1").Range(InCol & RowNum) = vbNullString Then
GoTo getnewrow
End If
TotNum = TotNum + Sheets("Sheet1").Range(InCol & RowNum)
If TotNum = InRng.Value Then
CheckCheques2 = True
ArCnt = ArCnt + 1
ReDim Preserve Arr(ArCnt)
Arr(ArCnt - 1) = RowNum
For Cnt = LBound(Arr) To UBound(Arr) - 1
If Sheets("Sheet1").Range(OutCol & Arr(Cnt)) = vbNullString Then
Sheets("Sheet1").Range(OutCol & Arr(Cnt)) = LetterArr(LetCnt)
Else
Sheets("Sheet1").Range(OutCol & Arr(Cnt)) = Sheets("Sheet1").Range(OutCol & Arr(Cnt)) _
& "," & LetterArr(LetCnt)
End If
Next Cnt
LetCnt = LetCnt + 1
End If
If TotNum < InRng.Value Then
ArCnt = ArCnt + 1
ReDim Preserve Arr(ArCnt)
Arr(ArCnt - 1) = RowNum
Else
ArCnt = 0
ReDim Arr(0)
TotNum = 0
End If
GoTo above
Else
GoTo above
End If
End Function




Private Sub CommandButton1_Click()
Dim LastRow As Integer, Cnt As Integer
Cnt = 1
Do Until CheckCheques2("A", "B", Sheets("Sheet1").Range("C" & 2)) Or Cnt = 1000
Cnt = Cnt + 1
Loop
'If Cnt < 200 Then
'MsgBox "DONE. Iterations: " & Cnt
''clear input
'With Sheets("Sheet1")
'LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'.Range("A2:A" & LastRow).Clear
'End With


'Else
'MsgBox "NO MATCH"
'End If


End Sub

[paulked]

I’ve amalgamated the bits I’ve done for you over the last day or two into one workbook. I think I’ve interpreted what you want correctly, but please shout out if I’ve missed something!

Explanation

1. Refresh Invoices from CSV:
a. If no CSV file is open or more than one CSV file is open, then it messages and does nothing.
b. If only one CSV file is open:
i. It clears the sheet of data and
ii. gets a summary of all the Invoices in that CSV file.
2. Search for Check:
a. Enter the check amount in the box.
b. If the figure entered matches any of the values from the CSV file it
i. highlights the line where it found it (them),
ii. lists the invoices found and
iii. puts an ‘X’ in column D
c. If it doesn't find a match it asks if you want to run Dave's code.
3. List Reconciled Invoices:
a. Copies all invoices marked with an ‘X’

You may want to change the ‘List Reconciled Invoices’ for ‘List Non-Reconciled Invoices’. That’s easily done.

[austenr]

hi paul,

this looks very good but am having trouble running it. I am getting a type mismatch error on the following line in daves macro:



TotNum = TotNum + Sheets("Sheet1").Range(InCol & RowNum)

Here is what I did to get to this point:

Opened the workbook you sent me.

Downloaded a csv file

selected 3 invoices from that file to get a total.

entered that total in the amount being searched for cell. i used 20.13, 154.02 and 62.04.

clicked the List Reconciled Invoices button

message box "amt not found in a single invoice" "do you want to run daves macro"

I've uploaded the csv file i used.

Both files were open when i ran it.

[paulked]

Hi

Strange, it worked for me, see attached workbook (note that I've added cell references in the 'box' so that there is no confusion over negative amounts).

I've also upgraded the csv handling so it now allows you to open one if there isn't one open or choose (by entering a number) if there is more than one open.

[austenr]

after ive worked with this a bit i figured it out and works as I want. Thank you. Only question is when the invoices are pulled in from the csv they show up as euros and not american dollars. is there a way to fix that? If I could suppress the message "Microsoft Excel is not responding" while it runs thru the iterations at times that would be great but i know not possible.

[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. :clap:

[paulked]

You are very welcome, take care :thumb

[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:

26417

Then I hit the 'List Reconciled' button and:

26418

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 :thumb

[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 :yes

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?

[paulked]

25 items are approx 33.5million combinations, which it just copes with in about 3 minutes. 26 numbers is approx 67million - 20 minutes, 30 is over a billion I'd guess at 24hrs!

[paulked]

One way would be to temporarlily remove numbers that couldn't possibly be a part of the payment (large numbers for instance). But when you have negatives that could be a problem, which is why negatives are bad!

[austenr]

thats what i thought. its hard to determine which ones dont belong to the payment. i suppose it still has a use for say 20 items. BTW, how do you determine the number of calcs necessary?

[paulked]

Google "how many combinations of 30 numbers" :thumb

[paulked]

Try a large one where you can leave it running over night. Put the lines in red into the code and it will show the number of seconds it took to do it (the Solvit code is in the mod_General module):



Sub SolvIt()
Dim lr As Long, i As Long, rw As Long, shS As Worksheet, shD As Worksheet, ar, rw2 As Long, res As Long, _
j As Long, cl As Long, n As Boolean, tm as Double
tm = Timer
n = True
If Range("L2") = "True" Then n = False
Set shS = shMain
Set shD = shData
If shS.Range("C5") = "" Then MsgBox "It looks like you have no data to match, C5 is empty!": Exit Sub
If IsError(shS.Range("F3")) Then
MsgBox "It looks like you have no Check to match, F3 is empty!"
shS.Range("F3").Select
Exit Sub
End If
If shS.Range("F3") = "" Then
MsgBox "It looks like you have no Check to match, F3 is empty!"
shS.Range("F3").Select
Exit Sub
End If
lr = shD.Cells(Rows.Count, 3).End(xlUp).Row
shD.Range("C4:C" & lr).ClearContents
shD.Range("D1:D10").ClearContents
lr = shS.Cells(Rows.Count, 3).End(xlUp).Row
shS.Range("C5:C" & lr).Copy shD.Range("C4")
shD.Range("G2:Q1000").ClearContents
With shD
.Activate
lr = .Cells(.Rows.Count, 3).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=Range("C4:C" & lr), SortOn:=xlSortOnValues, Order:=xlAscending
.Sort.SetRange .Range("C4:C" & lr)
.Sort.Apply
For i = 4 To lr
If .Cells(i, 3) > 0 Then Exit For
Next
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=Range("C" & i & ":C" & lr), SortOn:=xlSortOnValues, Order:=xlDescending
.Sort.SetRange .Range("C" & i & ":C" & lr)
.Sort.Apply
.Range("C2:C" & lr).Select
End With
startSearch
frmWrkng.Label1 = "Sorting data..."
DoEvents
res = shD.Cells(Rows.Count, 4).End(xlUp).Row
If res = 1 Then GoTo NoneFound
If res = 2 Then GoTo Only1
cl = 7
For j = 2 To res
ar = Split(shD.Cells(j, 4), ",")
rw2 = 2
For i = LBound(ar) To UBound(ar)
For rw = 5 To lr + 1
If shS.Cells(rw, 3) = shD.Cells(ar(i) + 3, 3) Then
shD.Cells(rw2, cl) = shS.Cells(rw, 1)
shD.Cells(rw2, cl + 1) = shS.Cells(rw, 2)
shD.Cells(rw2, cl + 2) = shS.Cells(rw, 3)
rw2 = rw2 + 1
End If
Next
Next
cl = cl + 4
Next
frmMulti.Show
res = frmMulti.Tag
Only1:
shS.Activate
ar = Split(shD.Cells(res, 4), ",")
rw2 = 5
For i = LBound(ar) To UBound(ar)
For rw = 5 To lr + 1
If shS.Cells(rw, 3) = shD.Cells(ar(i) + 3, 3) Then
If n Then shS.Cells(rw2, 6) = shS.Cells(rw, 1)
If n Then shS.Cells(rw2, 7) = shS.Cells(rw, 2)
If n Then shS.Cells(rw2, 8) = shS.Cells(rw, 3)
rw2 = rw2 + 1
shS.Range("A" & rw & ":D" & rw).Select
Selection.Interior.ColorIndex = 44
shS.Cells(rw, 4) = "X"
End If
Next
Next
Range("F3").Select
Msgbox Timer - tm
Exit Sub
NoneFound:
MsgBox "Sorry, no invoices matched the Check Value.", , "Oops!"
shS.Activate
Range("F3").Select
End Sub

[austenr]

thanks. ill give it a try.

[paulked]

When I tried with 30 positive numbers and 2 negatives (the -$3,175.89 list with some random ones I added), I got 357 unique combinations in 2.5hrs! How are you supposed to pick the correct one out of those?

I have limited the search to the first 10 it finds and it found those 10 combinations in under 10 seconds!

As soon as I took out the two negatives I got 4 combinations in 3 seconds.

I'd bank the cheque, then send the customer either a list of what you've allocated the money to or a list of outstanding and ask them to tick off what they've paid!

Anyway, latest version attached.

Cheers