Find the nearest row for direct parent

[hatudu95]

I am looking for a way to get Parent column populated. I tried multi row tool but doesnt work. Hi, the first 2 columns are the input and I am looking to get column parent  as desired output. 

That is the illustrative dummy data. the name could be anything but the ownership indicator is the key. So 1 means ultimate parent. 2 is direct child of ultimate parent. 3 is direct child of nearest 2 above it. For example, Direct parent of child D is C as C is nearest to D, not B 

Name	Ownership indicator	Parent
A	1
B	2	A
C	2	A
D	3	C (nearest above is C)
F	4	D
G	3	C
H	2	A

[abacon]

@hatudu95 Is the above table the desired output or the current output you are getting? It isn't clear what you are trying to do here.

Bacon

[Gaurav_Dhama_]

You just use join, i am assuming you are using ownership indicator to identify the parent.

lets call input is A,

Add formula tool to A output, add 1 to ownership. That will give 2 for A, 3 for B and C and so on.

once you have that, sort by owner in desc and id in Asc.

Now take unique group by on owner, that will leave you with, A 2, C3, etc.

join it with your initial input on indicator.

you will get the parent.

[jrlindem]

@hatudu95 are you literally trying to map:

• 1 = A
• 2 = B
• 3 = C
• etc.

Or is that just illustrative dummy data?  On the off chance you literally just need to map numbers to letters and you want to follow a traditional Alpha-ordering...

You could use a formula like:

Char([Ownership indicator] + 96) for lowercase letters
or
Char([Ownership indicator] + 64) for uppercase

I'm guessing this isn't what you're after, but just in case, I figured i'd throw it out there!  Otherwise, go with @Gaurav_Dhama_ 's response 😁

[hatudu95]

Hi, the first 2 columns are the input and I am looking to get column parent  as desired output

[hatudu95]

That is the illustrative dummy data. the name could be anything but the ownership indicator is the key. So 1 means ultimate parent. 2 is direct child of ultimate parent. 3 is direct child of nearest 2 above it. For example, Direct parent of child D is C as C is nearest to D, not B 

[hatudu95]

Hi, I tried to follow but couldnt. Do you have a workflow. Thank you very much

[aatalai]

@hatudu95 built it via a batch macro looking at each row in turn to find which row was the first to meet the ciretria and seems to work. Please take a look and let me know how you get on

[Gaurav_Dhama_]

Check out the attached workflow, it does not use any macro, since it does not get inside a loop, it will be faster in case you run it over a larger data.