Mapping one table based on another table range

W

waynehamilton

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

I have a mapping sheet (Map_Table) that has ranges for three differen
attributes Org Code, Account Code, Sub-Account Code. This mappin
sheet has six columns (one column for the min and one column for th
max for each attribute). The table also has a seventh column for th
account mapping if the account falls within each range. The Map_Tabl
is about 1,000 lines long. I have another sheet (Account_Map) tha
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:_*

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 t
look at each line in Map_Table. If the Org, Acct, and Sub i
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.
can write a If(And) forumla but this only covers one line the th
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 greatl
appreciate any help. Thanks in advance for your reply
 
M

Max

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<=Map_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 <at>yahoo<dot>com
----
waynehamilton said:
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:_*

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

waynehamilton

Thank you very much Max. I am struggling through the example. Mostl
trying to understand the formula. When I copy down to my working fil
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 tryin
to understand via Formula and I am still confused. I was not going t
ask for a sample file but thanks for posting. I will look at the fil
now
 
M

Max

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

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 <at>yahoo<dot>com. Pl drop a note here to alert
me if you're sending or not.
 
W

waynehamilton

Max, I must say that I am truly very grateful for your help and humble
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 everythin
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 somethin
for your troubles. If you do not get this email please let me know
 
M

Max

You're welcome. Glad to hear you got it to work !
I've received your private email and have replied to you.
Cheers
 
Top