Mapping one table based on another table range
 

Hello, any help would be greatly appreciated. I have tried to figure
this out but is has beat me down. Here is my deal:

I have a mapping sheet (Map_Table) that has ranges for three different
attributes Org Code, Account Code, Sub-Account Code. This mapping
sheet has six columns (one column for the min and one column for the
max for each attribute). The table also has a seventh column for the
account mapping if the account falls within each range. The Map_Table
is about 1,000 lines long. I have another sheet (Account_Map) that
list all of the accounts. This sheet has three columns – Org Code,
Account Code, Sub-Account Code. This table has about 5,000 lines.

*Map Table:*[/u]

*Org Org Acct Acct Sub Sub Line*_
From To From To From To Mapping
131 160 10000 10199 0 999 Account_1
131 160 11000 11000 0 0 Account_2
131 160 11300 11300 1 1 Account_3
131 133 11300 11300 910 910 Account_4
135 160 11300 11300 910 910 Account_5
131 160 11300 11300 991 991 Account_6
131 160 11300 11300 980 980 Account_7
131 160 11300 11300 960 960 Account_8
131 160 11300 11300 982 982 Account_9
131 160 11300 11300 985 985 Account_10
131 160 11300 11300 950 950 Account_11
132 132 11300 11300 965 965 Account_12



_Account_Map:_*

[b]Org Acct Sub Formula*[/indent]_
134 10000 1
131 10010 1
131 11000 0
134 11200 500
134 11300 900
132 11300 910
134 11300 910
134 11300 920


What I would like to do is create a formula in Account_Map sheet to
look at each line in Map_Table. If the Org, Acct, and Sub in
Account_Map are all in between the Org From & Org To, and Acct From &
Acct To, and Sub From & Sub To, pull the 7th column Hyperion value. I
can write a If(And) forumla but this only covers one line the the
Map_Table. I need a formula to that looks at each line in Map_Table.


If anyone has done this kind of formula in the past, I would greatly
appreciate any help. Thanks in advance for your reply.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

bttt



Thanks for the help


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

One way to try ..

Assuming data in sheet: Map_Table is in A3:G14

In sheet: Account_Map
-------------------------
Assume data is in cols A to C, from row3 down

Put in say, D3, and array-enter (press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(1,(A3=Map_Table!$A$3:$A$14)*(A3<=M ap_Table!$B$3:$B$14)*(B3=
Map_Table!$C$3:$C$14)*(B3<=Map_Table!$D$3:$D$14)*( C3=Map_Table!$E$3:$E$14)*
(C3<=Map_Table!$F$3:$F$14),0)),"",INDEX(Map_Table! $G$3:$G$14,MATCH(1,(A3=Ma
p_Table!$A$3:$A$14)*(A3<=Map_Table!$B$3:$B$14)*(B3 =Map_Table!$C$3:$C$14)*(B
3<=Map_Table!$D$3:$D$14)*(C3=Map_Table!$E$3:$E$14 )*(C3<=Map_Table!$F$3:$F$1
4),0)))

Copy D3 down

Col D will return the results: Account_1, Account_2, etc
or return blanks: "" for unmatched data in cols A to C
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"waynehamilton" wrote in message
...

Hello, any help would be greatly appreciated. I have tried to figure
this out but is has beat me down. Here is my deal:

I have a mapping sheet (Map_Table) that has ranges for three different
attributes Org Code, Account Code, Sub-Account Code. This mapping
sheet has six columns (one column for the min and one column for the
max for each attribute). The table also has a seventh column for the
account mapping if the account falls within each range. The Map_Table
is about 1,000 lines long. I have another sheet (Account_Map) that
list all of the accounts. This sheet has three columns – Org Code,
Account Code, Sub-Account Code. This table has about 5,000 lines.

*Map Table:*[/u]

*Org Org Acct Acct Sub Sub Line*_
From To From To From To Mapping
131 160 10000 10199 0 999 Account_1
131 160 11000 11000 0 0 Account_2
131 160 11300 11300 1 1 Account_3
131 133 11300 11300 910 910 Account_4
135 160 11300 11300 910 910 Account_5
131 160 11300 11300 991 991 Account_6
131 160 11300 11300 980 980 Account_7
131 160 11300 11300 960 960 Account_8
131 160 11300 11300 982 982 Account_9
131 160 11300 11300 985 985 Account_10
131 160 11300 11300 950 950 Account_11
132 132 11300 11300 965 965 Account_12



_Account_Map:_*

[b]Org Acct Sub Formula*[/indent]_
134 10000 1
131 10010 1
131 11000 0
134 11200 500
134 11300 900
132 11300 910
134 11300 910
134 11300 920


What I would like to do is create a formula in Account_Map sheet to
look at each line in Map_Table. If the Org, Acct, and Sub in
Account_Map are all in between the Org From & Org To, and Acct From &
Acct To, and Sub From & Sub To, pull the 7th column Hyperion value. I
can write a If(And) forumla but this only covers one line the the
Map_Table. I need a formula to that looks at each line in Map_Table.


If anyone has done this kind of formula in the past, I would greatly
appreciate any help. Thanks in advance for your reply.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

Here's a sample file with the implemented suggestion:
http://flypicture.com/p.cfm?id=46803
(Right-click on the link "Download File" at the top in the page)
File:waynehamilton_wksht.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thank you very much Max. I am struggling through the example. Mostly
trying to understand the formula. When I copy down to my working file
I get some #N/A on the accounts that I know have mapping.

I got to give it to you this is a one good formula. I have been trying
to understand via Formula and I am still confused. I was not going to
ask for a sample file but thanks for posting. I will look at the file
now.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

trying to understand the formula ..

Here's some explanations to help

The error trap " IF(ISNA(..),"",INDEX(...)) "
ensures that any unmatched cases will return blanks: ""
instead of ugly #N/As.

Stripping* away the error trap will reduce the formula length considerably
<g, and expose the "core" formula, i.e. the INDEX(...)

(*As a personal pref, I tend towards using error-traps, notwithstanding the
additional length this imposes on the formula)

In the INDEX(...), the essential matching work is dealt with by the part:

MATCH(1,(Cond1)*(Cond2)*(Cond3)*(etc),0)

where the product of all the conditions to be
satisfied, i.e.: (Cond1)*(Cond2)*(Cond3)*(etc)

will ultimately resolve to an array such as:
{0;0;0;1;0;0;0;0;0;0;0;0} (For cell D8 in "Account_Map")

where the "1" within the array will indicate all conditions satisfied

The position of the "1" within the array is then
returned by the MATCH, i.e. "4"

and INDEX(Map_Table!$G$3:$G$14, ...) then simply retrieves the 4th item
within the range Map_Table!$G$3:$G$14, i.e.: Account_4 as the final result

Btw, it is implicit that there'll be no overlapping conditions in Map_Table,
i.e. that each row in Map_Table clearly defines a unique, condition.

Hope the above clarifies a little more

I get some #N/A on the accounts that I know have mapping.

I suppose you mean blanks, since the error trap " IF(ISNA(..) "
would output unmatched cases as blanks: ""

I'm not sure if this could be due to any "gaps" within the unique conditions
specified in Map_Table, and/or because some numbers somewhere are text, thus
fouling up the calcs.

To take care of the latter, try copying any empty cell, then do a Paste
special Add OK to coerce any text numbers to real numbers. Do this for
cols A to F in Map_Table, and for cols A to C in Account_Map

If you like, send me a copy of your working file (zipped), and I'll take a
look. Send to: demechanik <atyahoo<dotcom. Pl drop a note here to alert
me if you're sending or not.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"waynehamilton" wrote in message
...

Thank you very much Max. I am struggling through the example. Mostly
trying to understand the formula. When I copy down to my working file
I get some #N/A on the accounts that I know have mapping.

I got to give it to you this is a one good formula. I have been trying
to understand via Formula and I am still confused. I was not going to
ask for a sample file but thanks for posting. I will look at the file
now.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

Max, I must say that I am truly very grateful for your help and humbled
by your knownledge. You get employee of the month.

I had an error in the formula that I orginally input from your reply.
Once I opened the attachment and studied the formula, I got everything
to work perfectly. The only error I had was a user error.

I will send you a separate email as I would like to send you something
for your troubles. If you do not get this email please let me know.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695

You're welcome. Glad to hear you got it to work !
I've received your private email and have replied to you.
Cheers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"waynehamilton" wrote in message
...

Max, I must say that I am truly very grateful for your help and humbled
by your knownledge. You get employee of the month.

I had an error in the formula that I orginally input from your reply.
Once I opened the attachment and studied the formula, I got everything
to work perfectly. The only error I had was a user error.

I will send you a separate email as I would like to send you something
for your troubles. If you do not get this email please let me know.


--
waynehamilton
------------------------------------------------------------------------
waynehamilton's Profile: http://www.msusenet.com/member.php?userid=1113
View this thread: http://www.msusenet.com/t-1870420695