I am trying to do something that I am sure is very simple to achieve, but it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve, but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

On Sheet2, starting in A1 make a table like this. Let's say it end at row
200
100 Y02
101 Y02
102 Y02
103 Y02
104 Y02
105 Y02
106 Y02
107 Y02
108 Y02
109 Y02
110 Y02
ABB Y02
ABC Y02
..
111 Y01
112 Y01
etc


On Sheet1 cell B10 holds child code 111
Cell C10 has formula =VLOOKUP(B10,Sheet2!A1:B200,2,FALSE)
It will return the Parent code
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve, but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent codes,
there are in excess of 5000 child codes. Some of the parents are linked to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve, but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

If Child Codes follow an alphabetical pattern this would be relatively easy,
is that the case. In other words if you sort the list of child codes would
the be grouped such that parent codes would be together?

Please also show us the layout of your data -
Child Code Parent Code
....
?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"cangiff" wrote:

Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent codes,
there are in excess of 5000 child codes. Some of the parents are linked to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve, but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

OK, you can build 2 tables and **they must be sorted in ascending order on
the left column**.

Build one for the number codes and a separate one for the alpha codes. Use
the *lower boundary* of each sorted sequence for the interval.

...........A..........B
1......100.......Y02
2......111.......Y01
3......121.......Y03
4......131.......Y02
5......141.......Y03

..........D..........E
1....AAA.....Y01
2....AAF.....Y03
3....ABB.....Y02

Then, with cell G1 as the input cell:

G1 = 118

=VLOOKUP(G1,IF(ISNUMBER(G1),A1:B5,D1:E5),2)

Returns: Y01

I can see where it would be a pita to build the table for the alpha codes!


--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent
codes,
there are in excess of 5000 child codes. Some of the parents are linked
to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the
resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve,
but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit
codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140,
ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150,
AAF-ABA

I would like a formula that will return which parent code applies for
any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

I am trying to create an easy reference function table for data that resides
within a networked General Ledger system.

The child codes can be a combination of alpha & numeric. If I sorted the
child codes, the parent codes would not run sequentially i.e.
child code 111 belongs to parent code Y01
child code 100 belongs to parent code Y02
child code 121 belongs to parent code Y03

An extract of the way that I currently have my data set out is

Parent Code Child code Child code
Range start Range end
Y01 111 120
Y01 AAA AAE
Y02 100 110
Y02 131 141
Y02 ABB ABF
Y03 121 130
Y03 141 150
Y03 AAF ABA
Y03

Thanks for the help

cangiff


"Shane Devenshire" wrote:

If Child Codes follow an alphabetical pattern this would be relatively easy,
is that the case. In other words if you sort the list of child codes would
the be grouped such that parent codes would be together?

Please also show us the layout of your data -
Child Code Parent Code
...
?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"cangiff" wrote:

Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent codes,
there are in excess of 5000 child codes. Some of the parents are linked to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve, but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140, ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150, AAF-ABA

I would like a formula that will return which parent code applies for any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

This looks like a good solution. I shall go away and put it into practise.

Many thanks to all
--
cangiff


"T. Valko" wrote:

OK, you can build 2 tables and **they must be sorted in ascending order on
the left column**.

Build one for the number codes and a separate one for the alpha codes. Use
the *lower boundary* of each sorted sequence for the interval.

...........A..........B
1......100.......Y02
2......111.......Y01
3......121.......Y03
4......131.......Y02
5......141.......Y03

..........D..........E
1....AAA.....Y01
2....AAF.....Y03
3....ABB.....Y02

Then, with cell G1 as the input cell:

G1 = 118

=VLOOKUP(G1,IF(ISNUMBER(G1),A1:B5,D1:E5),2)

Returns: Y01

I can see where it would be a pita to build the table for the alpha codes!


--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent
codes,
there are in excess of 5000 child codes. Some of the parents are linked
to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the
resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve,
but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit
codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140,
ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150,
AAF-ABA

I would like a formula that will return which parent code applies for
any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

Happy to help. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
This looks like a good solution. I shall go away and put it into practise.

Many thanks to all
--
cangiff


"T. Valko" wrote:

OK, you can build 2 tables and **they must be sorted in ascending order
on
the left column**.

Build one for the number codes and a separate one for the alpha codes.
Use
the *lower boundary* of each sorted sequence for the interval.

...........A..........B
1......100.......Y02
2......111.......Y01
3......121.......Y03
4......131.......Y02
5......141.......Y03

..........D..........E
1....AAA.....Y01
2....AAF.....Y03
3....ABB.....Y02

Then, with cell G1 as the input cell:

G1 = 118

=VLOOKUP(G1,IF(ISNUMBER(G1),A1:B5,D1:E5),2)

Returns: Y01

I can see where it would be a pita to build the table for the alpha
codes!


--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
Thanks for this. I can see that this will work, and I may have to go
down
this route.

However, I forgot to mention that although there are only 100 parent
codes,
there are in excess of 5000 child codes. Some of the parents are
linked
to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that
had
ranges of child codes instead of detailing individual codes as the
resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve,
but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit
codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140,
ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150,
AAF-ABA

I would like a formula that will return which parent code applies
for
any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff

1.In sheet1 create two colomn as
A B
Child Code Parent Code
2.In Sheet2 create the same columns with data as
Child Code Parent Code
111 Y01
112 Y02
3.Now click cell B1 in sheet 1
4.Insert following formula
=VLOOKUP(B2,Sheet3!A1:B6,2,TRUE)
5.Now drag the cells A and B down to apply the same formula to cells below.

Have a happy time€¦.



Chris
------
Convert your Excel spreadsheet into online calculator.
http://www.spreadsheetconverter.com

--
Chris
------
Convert your Excel spreadsheet into online calculator.
http://www.spreadsheetconverter.com

Message posted via http://www.officekb.com