Iterative loop

maverick_70

Hello,

I have a some what of a tricky workflow that I need help with.

I have a table which have the following information: It has 4 columns Part 1, Part2, Part1(m/b) and Part2(M/B).

Part1	Part2	Part1(M/B)	Part2(M/B)
ABC	a1234	M	B
ABC	b4321	M	B
CDE	a8989	M	M
CDE	a4344	M	B
CDE	a3434	M	B
EFG	a3131	M	M
EFG	a9999	M	M
a3131	a7979	M	B
HIJ	a6464	B	M
KLM	a8765	B	B
a8989	a6767	M	M
a6767	a8787	M	B
a9999	a5454	M	B
NMO	a8967	M	M

I am trying to build a table which would look like this.

Part	Result
ABC	a1234,b4321
CDE	a8787,a4344,a3434
EFG	a7979,a5454
a3131	a7979
HIJ	HIJ
KLM	KLM
a8989	a8787
a6767	a8787
a9999	a5454
NMO	a8967

The conditions I am using are

if Part1(M/B) = M and Part2(M/B) = B then Part2

if part1(M/B) = M and Part2(M/B) = M then look the value of part2 in the Part 1 column and again check for the conditions if part1(M/B) = M and part2(M/B) = M then check the value in part2 column in column 1 and check for the conditions again if part1(M/B)= M and part2(M/B) = B then part2

I am going down each level until Part2 M/B = B or if there is no part 2 in part1 column. The purpose is to go down at the deepest possible level.

The table with the result column is what I am looking for.

Your help would be greatly appreciated.

grossal

This was quiet a hard challenge, but surprisingly I found a way without going to Python (I was already typing a message "I'll use Python" in your answer window).

It won't win any beauty award and uses some trickery that isn't very clean, but it does the job.

Macro:

Main Workflow:

What happens in the macro:

- I join all rows (that haven't been resolved) with the lookup (just a copy of the original data)

- I do the lookup calculation and filter all rows to the top that got solved

- All rows that a still unsolved to go the bottom

- All formula tools in the macro help to do the trick. I have to concatenate and resolve the strings for the join to keep the original information in the data set

- The selects help to remove the joined columns

What happens in the main workflow:

- I solve everything possible but give it to the macro anyway (it'll filter out solved data)

- I use the filter to process the already sorted data

- I combine the already solved data with the macro solved data

- I use the Join tool on the original data to bring back columns that the macro missed because it couldn't find a lookup

- I union all together

- Formula to fix the rows that the macro couldn't solve because they had no lookup

I make one assumptions at the moment and that's "Part" has a length of 5 (all sample data did), but I already prepared it to be reworked when the length changes. We would only need to replace to formula tools with a Regex Tool if we want more flexibility with the length of the Part2 column. I already had some kind of headache and went with the easy way first. I'll grab a drink and be ready to adapt it for you in the next step, I just need a break for now 😃

Workflow and macro are attached. Let me know what you think.

Best

Alex

2020-04-30-iterative lookup with condition.yxzp