Excel help please - filter and fetch values

Hi team ,

Please help in finding a solution for the below problem -

I have the below data in attached excel -

There are 2 values -
curreny - USD
account - 7891011

I want to first loop through the “Remit_ID” values with “currency”.If the Remit_ID contains multiple matches for currency , it should check for “account values” and if there is a unique match for “Currency Account” in the 'Remit_ID" column it should fetch the corresponding ‘Supplier_ID’ value for that Remit_ID.
For eg. In the above case -

We have
curreny - USD
account - 7891011

So the remit_id match for these values is “EUR 7891011” and the corresponding value i.e “SUPPLIER_CONNECTION-3-3627” should be fetched.

and if currency - EUR
account - 4324424

we should get “SUPPLIER_CONNECTION-3-3627” value and since it is a unique match for currency and it should skip account match .

Regards,
Gokul
remit_data.xlsx (10.1 KB)

@gokul1904
Interesting i can try. Please wait

1 Like

RemitData.xaml (8.2 KB)
Hello,
please find the solution and let me know if it’s match the logic you want :slight_smile:

1 Like

@gokul1904
the task can be handled as group by case

As an alternate we can rewrite mybe to a filter / Lookup case. A building block is to Split the Remit_ID Value into Currency and Account. For this step Regex can help us

1 Like

Hi @MSu thank you for the response .
When there is no currency match it should check the match for the last 4 digits of the “account” in Remit_ID field and return the Supplier_ID corresponding to that .

For eg. For the above data , if we have currency as “BRA” and account number as “987893456” then Supplier_ID - SUPPLIER_CONNECTION-3-2345 should be the extracted.

@gokul1904
we would recommend to handle more complete the requirements and its description
a more clear and complete requirement description results to a more fast solution suggestion

Currently:

  • the datasamples and the mentioned scenarios do not match
    • e.g. we have currency as “BRA” and account number as “987893456” then Supplier_ID - SUPPLIER_CONNECTION-3-2345

we dont see: 987893456 so what is the rule of returning: SUPPLIER_CONNECTION-3-2345

Same for

Thanks for support

I have made a more elaborate post here considering all the points mentioned -

Regards,
G

Duplicate of Find match for rows in excel