Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assistance needed (03)

    I am going thourgh a mapping exercise of department/account combinations which get mapped to a different account (see Table A). I could write code to list each and every combination but that would exceed the 65,536 row limitation of Excel and doesn't seem to be the best way to achieve the mapping as not all dept/acct combinations are used. Therefore my question of a better way of doing the mapping based on the table.


    <table border=1><td>Table A</td><td></td><td>Dept</td><td>Dept</td><td></td><td>Acct</td><td>Acct</td><td>Map To</td><td></td><td>From</td><td>To</td><td></td><td>From</td><td>To</td>
    <td>AA1000</td><td></td><td>0</td><td>999</td><td></td><td>11050</td><td>11099</td>
    <td>AA1010</td><td></td><td>0</td><td>999</td><td></td><td>10111</td><td>10119</td>
    <td>AA1020</td><td></td><td>200</td><td>200</td><td></td><td>13000</td><td>13000</td>
    <td>AA1100</td><td></td><td>300</td><td>300</td><td></td><td>24000</td><td>24599</td>
    </table>
    The departments and accounts that I am trying to map are listed it their respective columns.

    Example:
    <table border=1><td>Table B</td><td></td><td>Dept</td><td>Acct</td><td></td><td>Should Map To</td>
    <td></td><td></td><td>21</td><td>11052</td><td></td><td>AA1000</td>
    <td></td><td></td><td>200</td><td>13000</td><td></td><td>AA1020</td>
    <td></td><td></td><td>300</td><td>24500</td><td></td><td>AA1100</td>
    </table>


    Thanks for your assistance,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    Apparently, there is overlap between the department ranges (200 and 300 both fall within the range from 0 to 999).
    Can there be overlap between the account ranges?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Hans,

    There would not be an overlap in the account.

    One a second note...in my original post there is a gap between the first paragraph and the table. How would one go about reducing the size of the gap? I typed the first paragraph and then the next line starts the table. I'm curious as to how the gap between the two appears.

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    If there is no overlap in the accounts, the lookup can ignore the department and look at the account only. See the attached workbook for an example of a formula to do this.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    The distance between the text and the table following it appears to be a quirk of the Lounge software, it's not your fault as far as I can see.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Hans,

    I'll incorporate it into my model.

    Thank you,
    John

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Hans,

    Upon further investigation there is an overlapping of accounts. The department/account combination is important in the overall mapping process.

    Regards,
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    Can you provide some realistic examples?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    If the combinations of department and account are unique, you should be able to adapt the formulas in the attached version.
    Attached Files Attached Files

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Hans,

    Thank you,
    John

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Hans,

    One last tweak if you don't mind.

    If you recall in the formula there is a reference to ROW. As I add to the table the row count increases ie 5 to 6 and etc.. What I would like to achieve is pass the value returned from a derived range. I have attached a sample file containing additional information as to what I would like to achieve.

    =INDEX($A$3:$A$7,SUMPRODUCT((L5>=$F$3:$F$7)*(L5<=$ G$3:$G$7)*(K5>=$C$3:$C$7)*(K5<=$D$3:$D$7)*ROW($1:$5)))

    Thanks,
    John
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance needed (03)

    See the attached version. I created dynamic ranges for all columns in the formula, it now looks like this in O2:

    =INDEX(MapTo,SUMPRODUCT((L2>=AcctFrom)*(L2<=AcctTo )*(K2>=DeptFrom)*(K2<=DeptTo)*ROW(R_TableRows)))
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assistance needed (03)

    Thank you.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •