Reading Data from Multiple combinations across files.

Question

Good Day Everyone

I have a complex problem that I need suggestion on.

I have an excel file(attached here) tab 1 that has a lot of information. I need to calculate the SETTINGS field(AV). How this works is as follows

Combination of the fields System, Client, POrg, Pgr, Type and PO Total Amt USD is compared with System, Client, Characteristics Value, Order Type and Purchasing Organization from the SETTINGS tab. If everything matches then the value from the Implementation field in SETTINGS tab is populated as Settings field in Sheet 1.

For Example - The highlighted orange field, combinations are searched in the SETTINGS tab and based on the combinations USUO is populated from Settings tab to Sheet 1.

unknown · Answer

Thank you for your response.

Well most of the join condition works but one combination has a range. For Example

Sheet 1
Code - 123
P Number - 1
Q Number - 2
R Number - 3
Amount - 5000

Sheet 2
Code - 123
P Number - 1
Q Number - 2
R Number - 3
Net Amount Range <10000
If TRUE THEN EXTRACT

(Implementation - ABCD)

If the above matches then extract the field Implementation(ABCD) from Sheet 2 and add it as RS Settings in Sheet 1

Similar scenario

Code - 321
P Number - 10
Q Number - 20
R Number - 30
Amount - 50000

Sheet 2
Code - 321
P Number - 10
Q Number - 20
R Number - 30
Net Amount Range > 10000

(Implementation EFGH)

If the above matches then extract the field Implementation(EFGH) from Sheet 2 and add it as RS Settings in Sheet 1

Qiu · Answer

@Inactive User 
We can basically do a joint, but I found it is not so clear about your matching cretiria.

Just a sample and you may develope it as per your intention.

New Workflow5.yxmd

atcodedog05 · Answer

Hi @Inactive User

This should be possible using left full join tool and mapping on multiple key columns. If it matches takes values from settings tab if not keep the values from sheet1.

Workflow:

1. Using join tool and joining on mapping keys. Deselecting settings column from left (sheet1) and selecting Implementation column from right (settings), renaming it to settings and deselecting all other columns from right. Note: you can use option button in top of the select window to deselect all right columns.

2. Using union tool to do left full join to get all old rows and changed rows from sheet1.

I am was not able to successfully map the columns since I didn't know the mapping combination. Since you would have it you will be able to do it.

Hope this helps 🙂

settings.yxmd