Please help!!

Question

Hi can someone help me to solve this.

I have two sheets and nothing is common between them to join the fields.

In the change sheet i have some changes related to some applications where ABC is a svp /approver

and in second sheet i have application name and its id associated with the person eg ABC ( application associated with person’s coverage area)

i want to mapp the file means i want to take changes sheet and use names of application from columns- affected business application,affected class and affected application and with the similar name link that with application id in the second sheet.

eg : i want to  find all applications under ABC.

note: apps might be separated with commas in changes sheet as shown in affected class column: CMS, ESG, ART - U.K

i.e CMS IS ONE APPLICATION

ESG is the other same is with other two columns.

i am finding it little tricky . Can someone please help me to solve this . It would be highly appreciated.

Application Sheet.xlsx

Changes sheet.xlsx

Gawa · Answer

@Navya08  I got it. You want to JOIN data but value of two table are not exactly match: You need fuzzy match in other word.

Now, solution can be by using Appendix Field(=Cross Join) for checking all combination.

As I'm not sure the context of data, so this is my assumption: JOIN condition is value in one table contains value in another table. This can be achieved by Contains() function. Here is the revised WF. Please see how it works. If JOIN condition is not like this, you can modify it  so as to fit to your demand.

1243259_gawa_REV1.yxzp

Navya08 · Answer

@gawa thank you for the help.

but if you see the changes sheet we have affected class 5th row- CMS,ESG,ART-UK with approver svp-ABC

and application sheet has row 4:cms research description as CMS

so this should be the output but my output is blank.

i want the output where even if in description or name the application name exists it should give me the output:

case1: with exact match of the name

cas2: with like condition eg CMS and after  that anything can be there

IMG_7529.jpeg

IMG_7528.jpeg

Gawa · Answer

hi @Navya08

I think you have to transpose data, and parse into rows to get all application name like this.

Unfortunately, no records are match between Application sheet and Change sheet, but it should be because the given data is incomplete to be shared in this community. Hope this would work well your real dataset.

1243259_gawa.yxzp