Formula Help

[KBrown]

I need help with a formula...

I have two columns of data and I need to figure out a way to have Excel
figure out the third column. The data is being imported into a database and
will be creating a tree structure.

The two columns I have are Level (indicates which level in the tree
structure this record needs to be at) and number (a unique number for this
record).

I need a formula (if there is one) that will determine the Parent Record.
So in the example below, I have filled out the Parent Code for several of the
records. The parent for "3: M3.A.1" is "3: M3.A". Is there a way to have a
lookup or vlookup do this work for me?

Level Number Parent Code
1 3: M3.A
2 3: M3.A.1 3: M3.A
3 3: M3.A.1.1 3: M3.A.1
4 3: M3.A.1.1.1 3: M3.A.1.1
4 3: M3.A.1.1.2 3: M3.A.1.1
4 3: M3.A.1.1.3 3: M3.A.1.1
4 3: M3.A.1.1.4 3: M3.A.1.1
4 3: M3.A.1.1.5 3: M3.A.1.1
3 3: M3.A.1.2
4 3: M3.A.1.2.1
4 3: M3.A.1.2.2
3 3: M3.A.1.3
4 3: M3.A.1.3.1
4 3: M3.A.1.3.2
4 3: M3.A.1.3.3
2 3: M3.A.2
3 3: M3.A.2.1
4 3: M3.A.2.1.1
4 3: M3.A.2.1.2
4 3: M3.A.2.1.3

 

[Lars-Åke Aspelin]

I need help with a formula...

I have two columns of data and I need to figure out a way to have Excel
figure out the third column. The data is being imported into a database and
will be creating a tree structure.

The two columns I have are Level (indicates which level in the tree
structure this record needs to be at) and number (a unique number for this
record).

I need a formula (if there is one) that will determine the Parent Record.
So in the example below, I have filled out the Parent Code for several of the
records. The parent for "3: M3.A.1" is "3: M3.A". Is there a way to have a
lookup or vlookup do this work for me?

Level Number Parent Code
1 3: M3.A
2 3: M3.A.1 3: M3.A
3 3: M3.A.1.1 3: M3.A.1
4 3: M3.A.1.1.1 3: M3.A.1.1
4 3: M3.A.1.1.2 3: M3.A.1.1
4 3: M3.A.1.1.3 3: M3.A.1.1
4 3: M3.A.1.1.4 3: M3.A.1.1
4 3: M3.A.1.1.5 3: M3.A.1.1
3 3: M3.A.1.2
4 3: M3.A.1.2.1
4 3: M3.A.1.2.2
3 3: M3.A.1.3
4 3: M3.A.1.3.1
4 3: M3.A.1.3.2
4 3: M3.A.1.3.3
2 3: M3.A.2
3 3: M3.A.2.1
4 3: M3.A.2.1.1
4 3: M3.A.2.1.2
4 3: M3.A.2.1.3

Try the following formula in cell C3:

=INDEX(B$1:B3,MAX((A$1:A2<A3)*(ROW(A$1:A2))))

Note: This is an array formula that must be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Copy down as far as needed.

Hope this helps / Lars-Åke

 

[Luke M]

Since it appears that the parent code is directly imbedded into the
name/number, why not do something like:

=IF(A1=1,B1,LEFT(B1,LEN(B1)-2))

If it's possible for the last digit to be greater than 9, this formula is
more flexible (but longer)

=IF(A1=1,B1,LEFT(B1,FIND("xxxx",SUBSTITUTE(B1,".","xxxx",LEN(B1)-LEN(SUBSTITUTE(B1,".",""))))-1))

 

[Pete_UK]

As long as you only have single digit numbers between the full-stops,
as in your example, then you could use this in C2:

=IF(COUNTIF(B$2:B2,LEFT(B2,LEN(B2)-2))>0,LEFT(B2,LEN(B2)-2),"")

and copy this down as far as needed.

Hope this helps.

Pete

 

[Jacob Skaria]

Suppose you have your data in ColA,B,C try the below formula in cell D2 and
copy down as required...

=IF(A2>1,TRIM(LEFT(SUBSTITUTE(B2,".",REPT(" ",255),A2),255)),"")

If this post helps click Yes