Index Match in Blank Records - Not Using Join or Union Tool

[SomuGS]

Hi,

I'm trying to find/return a result based on matching a certain value in the same data set.

1. Input is the same data set or data table.

2. Issue  - There are blank records (row values) that needs to be filled with a result by matching a unique ID from corresponding value in the data set but in a different column.

3. I'm looking use a function/formula rather than using a join tool and later use union tool to merge data.

Any help would be appreciated.

[ShankerV]

Hi @SomuGS 

Yes, you can use the formula tool. (Use contains formula).

Sort the data based on Destination code.

Then, use multi row field tool to fill the empty rows in last column.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @SomuGS 

Please find the sample use case related to your dataset.

Input was:

Hope this helps!!!!

If you have any more questions, please let us know!!!

Many thanks

Shanker V

[SomuGS]

Hi @ShankerV 

Thank your for the solution.

I'm trying to use minimal tools and want to keep the data in the same order as original and don't want to add sort tool to break the order of data.

Thanks for giving it a try and I will also poke around to see if something can be done.

[ShankerV]

Hi @SomuGS 

Here is one way to get the original order back.

[SomuGS]

Hi @ShankerV,

Even I thought about this. However, this is adding more tools to workflow and my goal is to have minimal tools and to use formula tool in such instances as much as possible. Thanks!