Formula help

[austenr]

Hi guys Im trying to insert this formula


=IF(ISNUMBER(MATCH(ROWS(1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")

into a cell in Excel 2007. I get one of those errors that says "Excel has found and error to accept click Yes"

Ive attached the sample workbook if someone could point out my error. Also much appreciated if anyone has a suggestion to do it a different way. The goal is to put an "X" next to the numbers that make up the number in C2.

[Dave]

I'm confused. There's multiple ways of adding up to the number in C2. So, pretty much all of the numbers could have an "X" beside them.... depends which ones U want to add together. Dave

[austenr]

I'm confused. There's multiple ways of adding up to the number in C2. So, pretty much all of the numbers could have an "X" beside them.... depends which ones U want to add together. Dave

Heres what I am after not sure if this was the right approach to take. Found the above online and was trying to modify it.

We get a lot of checks to post that come with no remittance. So I am looking for a way to copy and past the invoice totals in our system in column A and then have a cell you can input the total of the check the customer sent in. After the total is entered have a way to either highlight the invoices that make up the total or a way to show all groupings of invoices that make up the total entered by the user. Hope that makes it clearer.

I thought about SUMPRODUCT or something like that but I am not that good at it to get that to work or if it even will. Thanks for responding.

[Dave]

Don't think U can do it with a formula solution. Here's some VBA that seems to work. HTH. Dave

Option Explicit
Public Sub CheckCheques(InCol As String, OutCol As String, InRng As Range)
Dim LastRow As Integer, LoopCnt As Integer, 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")
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
MsgBox "DONE"
Exit Sub
End If
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
TotNum = TotNum + Sheets("Sheet1").Range(InCol & RowNum)
If TotNum = InRng.Value Then
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 Sub
To operate...

Call CheckCheques("A", "B", Sheets("Sheet1").Range("C" & 2))

[austenr]

Thanks for the code. Could you put that in a workbook and post it. Not sure where the sub goes. Thanks again.

[Dave]

I would prefer not to post a workbook. Just copy and paste the Sub code to the sheet code. Add an active X command button to the sheet from the developer tool box. Right click on the button in design mode and select view code. Copy and paste the operate code. It works with the file U posted. Dave

[austenr]

I would prefer not to post a workbook. Just copy and paste the Sub code to the sheet code. Add an active X command button to the sheet from the developer tool box. Right click on the button in design mode and select view code. Copy and paste the operate code. It works with the file U posted. Dave


Thanks for the explanation. I’ll give it a go.

[austenr]

Thanks Dave works great...one thing i noticed was that if you clear the x,y,z from column B and click the button again you get different results. Is that supposed to happen? In other words column B is filled with different results.

[Dave]

Hi austenr. There are many, many combinations of numbers in your example that can combine to equal the example total. The code only identifies 3 possible combinations and will likely be different each time. I suspect, that with your real world data that contains cents (ie. data $10.49 and total $480.56) there will probably be only one possible outcome... or just a few anyways. The code clears previous output before it generates new output. I'm guessing with more data, you will need to increase the loop counter from 1000 to maybe 10000 or more dependent upon how much data you have. Dave

[austenr]

Thanks Dave I thought that may be the case and real world data will have cents. Im marking this solved and thanks for your help. :thumb

[Dave]

You are welcome. Thanks for posting your outcome. Dave

[austenr]

You are welcome. Thanks for posting your outcome. Dave

Much thanks to Dave for helping me sort this out. After some more testing the only way it works is if the negative numbers in column A are removed. Most times there are instances where there will be credits (neg numbers) that make up the balance I am looking for with the total in Col C.

I was hoping that the below could be altered in some way to include negative numbers in the search for the total.


Option Explicit

Public Sub CheckCheques(InCol As String, OutCol As String, InRng As Range)
Dim LastRow As Integer, LoopCnt As Integer, 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")
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
MsgBox "DONE"
Exit Sub
End If
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
TotNum = TotNum + Sheets("Sheet1").Range(InCol & RowNum)
If TotNum = InRng.Value Then
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 Sub

[Dave]

Hi again austenr. Not sure if I understand. In the example wb data, if you change the numbers to negative they will still be correctly used as part of the collection of numbers to achieve the total. Do you mean the negative values should be treated as positive in the total summation? Dave

[austenr]

Hi Dave. Im posting the Workbook so you can see what I mean. In the example the total I am looking for is 212.49. Deleting the negative numbers in col A and clicking the button gives the desired result except there is a letter in column B after you delete the negative numbers.

So run the workbook as is and you will get no matches. Delete the negative numbers and re run it and you will see what I mean.

In the example rows 6 and 7 should match.

Sometimes the negative numbers (credits) need to be deducted to get the correct amount.

[Dave]

Do you ever want to search for negative numbers and/or are negative numbers ever part of the total? Dave

[austenr]

No the total is always positive. However like i said earlier there could be negative numbers in col A that makes up the total im looking for in col C.

[Dave]

So, you may have -10.21 and 30.55 and you are looking for a total of 20.34? Dave

[austenr]

thats right.

[Dave]

The previous code actually works eventually. This seems to work every time. Dave

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")
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
To operate...

Cnt = 1
Do Until CheckCheques2("A", "B", Sheets("Sheet1").Range("C" & 2)) Or Cnt = 200
Cnt = Cnt + 1
Loop
If Cnt < 200 Then
MsgBox "DONE. Iterations: " & Cnt
Else
MsgBox "NO MATCH"
End If

[austenr]

ill give it a go. i see the operate code is different and the sub has changed to a function. Where do i put the function in a standard module? Also im not sure what you mean works eventually?

[Dave]

Same as before as far as code placement. If U hit the button enough times before the code would eventually produce a result. Dave

[austenr]

Nice Dave. Works great. Thanks.

[Dave]

Once again austenr, you are welcome. You may need to adjust the "200" iterations dependent upon the amount of data. Have a nice day. Dave

[austenr]

Hi Dave been playing around and testing it some more. Im running into a problem if you copy and paste over the numbers in col A and you are certain that you have those invoices in that list that match the total and run the function it does not come back with any matches. However if you clear the numbers and input them manually it works. Am I missing something.

Also should note the numbers i am pasting to the workbook are from a csv file if that matters.

Also if you save the workbook as an xlsx file the copy and past the numbers it works. Not sure why.

[Dave]

Trial changing this line of code...

TotNum = TotNum + CDbl(Sheets("Sheet1").Range(InCol & RowNum))
I think you're pastings strings. Dave

[poielsd]

I'm confused. There's multiple ways of adding up to the number in C2. So, pretty much all of the numbers could have an "X" beside them.... depends which ones U want to add together. Dave