Formula Help

  • Thread starter Thread starter KBrown
  • Start date Start date
K

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
 
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
Click to expand...


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
 
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))
 
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
 
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
 
Back
Top