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

On Tue, 10 Nov 2009 10:17:02 -0800, KBrown
wrote:

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

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"KBrown" wrote:

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

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


On Nov 10, 6:17*pm, KBrown wrote:
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 *

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

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

If this post helps click Yes
---------------
Jacob Skaria


"KBrown" wrote:

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