display parent in where there is a parent child relationship - Indentured level

[gheyman]

need to display parent, in parent column, where there is a parent child relationship based on an indentured level listed in a column
Column A: Indentured Column B: Part Number Column C: Parent

Indenture Part Number Parent
1- ABC
2- 123
3- 123.1x
3- 12ab
2- 455
3- pqp
4- 1@233

I am looking for a formula (or code) that would fill in column C - "Parent"

The results would look like this;

Indenture- Part Number- Parent
1- ABC-
2- 123- ABC
3- 123.1x- 123
3- 12ab- 123
2- 455- ABC
3- pqp- 455
4- 1@233- pqp


There could be numerous row, so if its code...

 

[MickG]

Try this for data starting "A2"

[COLOR="Navy"]Sub[/COLOR] MG13Feb34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    .Add Dn.Value, Dn
[COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value - 1) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Item(Dn.Value - 1) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(Dn.Value - 1)
            [COLOR="Navy"]If[/COLOR] R.Row < Dn.Row [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 2).Value = R.Offset(, 1).Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[gheyman]

Thanks, Mick

But when I run it nothing happens.

 

[MickG]

Try this example.
It should work unless your running a "Mac" !!
https://app.box.com/s/0ro9n398vb4x8t7war645evnf1vb8x3n

 

[gheyman]

Thanks Mick but I cant seem to get it to work for some reason. I run the code and literally nothing happens.

 

[MickG]

Have you run macro's before in your workbooks???
If not Google "Enable or disable macros in Office files"
Also have you removed the values in columns "C" (Yellow cells-(Results)) before you run the Code.

 

[gheyman]

Its a formatting issue. I took your sheet and copied the Indentured levels and pasted them over mine. Once I did that, it ran.

(The data in columns A and B are pasted into my sheet)

If I go into cell A2 and retype the number in, it runs. For some reason its not liking the pasted in data in column A

 

[MickG]

I'm Glad you have it working.