First I have to check if [first 2 characters of " currency" ] is present in Remit_ID rows.If it has a unique row match , it should return the corresponding “Supplier_ID” for that Remit_ID. If its present in multiple rows or if no rows match, it should check for the [last 4 digits of " account"] in Remit_ID rows.If there is a unique match it should return the corresponding “Supplier_ID” for that Remit_ID.
For example -
currency - USD
account - 23421134
it should return - SUPPLIER_CONNECTION-3-3628
currency - BRA
account - 23423456
it should return - SUPPLIER_CONNECTION-3-2345
currency - BRA
account - 234234561
it should return - “No record found”
currency - USD
account - 7891011
it should return - SUPPLIER_CONNECTION-3-7654
For multiple matches even after currency and account validation , it should return any of the
Supplier_ID
I have flagged the original post and asked the moderator to delete it since the requirement is not precise and I havent achieved the desired solution on that.The requirements for this is different from the original one.
Thanks a lot for the prompt response. but this list is a dynamic one where switchcase cannot be used
“currency” and “account” are inputs to this excel datatable
it should perform a match with first 2 characters of currency and last 4 digits of account number in the order as mentioned in the main post.
(From d In dtData.AsEnumerable
Let sc = strCUR.Trim.Substring(0,2).toUpper
Let sa = strACC.Trim.Substring(strACC.Trim.Length - 4).toUpper
Let rm = d("Remit_ID").toString.Trim.toUpper
Where rm.StartsWith(sc) Or rm.EndsWith(sa)
Let cm2 = Convert.ToInt32(rm.StartsWith(sc)).toString
Let cm1 = Convert.ToInt32( rm.EndsWith(sa)).toString
Let msk = CInt(cm1 & cm2)
Where msk > 0
Order By msk Descending
Select v = d("Supplier_ID").toString).DefaultIfEmpty("No record found").First()
Thank you @ppr for your response.However it is not giving desired output for 1st and 4th scenario -
I will elaborate the first example → [first 2 characters of “Currency” i.e USD] has multiple matches in rows 1,2,4.So it should validate with [last 4 digits of " account"i.e 1134].It has only one match in row 4.Hence it should give SUPPLIER_CONNECTION-3-3628 as output.
If [first 2 characters of “Currency” i.e USD] had a unique match , it should have given respective Supplier_ID and it wont validate for [last 4 digits of " account"i.e 1134]
Only when currency validation results in multiple matches it should proceed with the account validation to filter the multiple matches.
