Hello everyone,
I have an analysis I want to make, but I don't know how to do it. Maybe one of you can help me out. The problem is as follows:
- There are two excel datasets, one in which you have a list with numbers (invoicenumbers). The other one contains a list with text and numbers mixed (bank statements).
What I want to do, is filter the bank statements in such a way that all the rows where at least one invoicenumber appears in a cell (matches), the row remains (is filtered). When there is no match between an invoicenumber and a bank statements, the row should be discarded. The problem is, that sometimes multiple invoicenumbers are paid at once. So what I mean is that in one cell, sometimes multiple invoicenumbers are stated. If at least one of the invoicenumbers from the list with invoicenumbers matches with the content of a cell, the row should remain.
I hope you guys understand what I mean. Please be elaborative in your reply as I am not that good in IDEA yet. I don't have a clue on how to solve this problem in Excel either....
Mike
MikeF
Offline
Last seen: 4 years 6 months ago
Joined: 11/08/2016 - 05:11
Hi MikeF,
how to import the excel datasheet should be no problem right?
The problem with more than one invoicenumber in a field maybe could be solved this way:
Sub Main'Variables for the content of your table
Dim sInvoiceField1 As String
Dim sInvoiceField2 As String
'Variables what number you are looking for
Dim sInvoiceSearch1 As String
Dim sInvoiceSearch2 As String
'better to clear first all variables
sInvoiceField1 = ""
sInvoiceField2 = ""
sInvoiceSearch1 = ""
sInvoiceSearch2 = ""
'Example A - One number to match
sInvoiceField1 = "2017-0001"
sInvoiceSearch1 = "2017-0001"
If iIsIni(sInvoiceSearch1, sInvoiceField1) <> 0 Then
MsgBox "sInvoiceSearch1: " & sInvoiceSearch1 & " was found",0,"Result A"
MsgBox "Invoice was found at position : " & iIsIni(sInvoiceSearch1, sInvoiceField1),0,"Position - Result A"
End If
'Example B - More than one number, but with match
sInvoiceField2= "2017-0003, 2017-0004, 2016-0001"
sInvoiceSearch2 = "2017-0004"
If iIsIni(sInvoiceSearch2, sInvoiceField2) <> 0 Then
MsgBox "sInvoiceSearch2: " & sInvoiceSearch2 & " was found",0,"Resul B"
MsgBox "Invoice was found at position : " & iIsIni(sInvoiceSearch2, sInvoiceField2),0,"Position - Result B"
End If
'IsIni is looking for matching content. Should be more detailed in the IDEA help.
End Sub
IsIni you could use as "eqn" or also as a join condition.
Cheers,
Chris
Hi MikeF,
This sounds like it could be done with a couple of virtual fields and a mathes only join, but I'm having trouble visualizing your dataset. It would be very helpful if you could uploaded a small sanatized sample of each spreadsheet that contain at least the fields you mentioned above. I would then be able to give you detailed instructions of the steps to take in IDEA to solve your problem.
Regards,
Steve
Here is a ISINI Join script that I created. I think it may do what you want, or at least identify matches. It will take a long time on large data sets.