Excel help please - filter and fetch values

[gokul1904 (Glory 01)]

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)

[raja.arslankhan (Raja Muhammad Arslan khan)]

@gokul1904
Interesting i can try. Please wait

[MSu (Malak Alsufaian)]

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

[ppr (Peter Preuss)]

@gokul1904
the task can be handled as group by case

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

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

[gokul1904 (Glory 01)]

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.

[ppr (Peter Preuss)]

@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

@gokul1904

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 .

Thanks for support

[gokul1904 (Glory 01)]

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

Find match for rows in excel

Hi team, I have below excel data - [image] I have 2 values - “currency” and “account” 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. F…

Regards,
G