+ Reply to Thread
Results 1 to 27 of 27

Account & Dept Mapping

  1. #1
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Account & Dept Mapping

    I'm trying to improve our account mapping between two different systems. Right now, I'm adding sumifs to get my result which is really cumbersome and doesn't allow for efficient maintenance. I'm hoping there is a way to create a worksheet which holds all of my rules, and use a formula to pull the data based on those rules. Here is an example of my rules:

    Cost Type Dept Account
    Payroll 10, 15 81110, 81115, 81120
    Payroll 20, 21 81110, 81115, 81120
    Travel 10, 15 87050, 87060
    Travel 20, 21 87050, 87060

    So currently I'm using a sumifs like this =sumifs(Amount, Acct 1, Dept 1)+sumifs(Amount, Acct 2, Dept 1)+sumifs(Amount, Acct 3, Dept 1)+sumifs(Amount, Acct 1, Dept 2)+sumifs(Amount, Acct 2, Dept 2)+sumifs(Amount, Acct 3, Dept 2)

    Surely there is a better way to do this, right?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    Maybe you can use sumproduct in this way... =SUMPRODUCT((Account={Acct 1,Acct 2,Acct 3})*(Dept={Dept 1,Dept 2,Dept 3})*Amount)
    I've tried something similar but it may need to have a semicolon on the second set ={Dept 1;Dept 2;Dept 3}
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    I thought that might be a solution, but it doesn't overcome the issue of maintenance. For example, if a new account is added to the payroll cost account, I would need to go back and update every row for the payroll cost type.

    Also, I'm always leery of using sumproduct, but that may be because I'm not used to using it. Whenever I've used it in the past, I've had to hit ctrl+enter (or maybe it was shift+enter). Does this only apply the FIRST time you use the formula or do you have to do that every time your data refreshes?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    what I gave you is not an array formula, you don't need to hit ctrl/shift/enter to use it. It is hard to give you a better formula from just a quick post without any real details.
    If you are dealing with changing details maybe a pivot table or using power query or possible VBA.

  5. #5
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,437

    Re: Account & Dept Mapping

    for account and department mapping you need mapping tables and then create a new table that will lookup the correct mapping for the other system.
    that way all you have to do is maintain the account and the department table and all the formulas that build the import file again and again.

    You will need to make a clear example file to be able to help you the 4 lines in the opening post are to unclear to create any kind of mapping.

  6. #6
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    Gotcha. Thanks for the clarification on the array formula.

    I've attached an example of what I'm dealing with. The hope is that I could add an account to the mapping tab, and the Output tab would automatically be updated.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    Try this array formula in C3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Because it is an array formula you have to finish with Ctrl + Shift + Enter. Then copy down.
    Last edited by Jacc; 04-25-2019 at 01:28 PM.

  8. #8
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    What is supposed to be in A12 and B12?

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    I altered the sumproduct to your numbers but for now I am not sure based on the way you structured the data how to make one that looks at the left and right in the output tab under depts.

    I'm returning your workbook with a couple different formulas and with a pivot table that you can adjust in a new tab.
    Attached Files Attached Files

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    looking at Jacc's, I think you have to change A12 and B12 to A3 and B3.

  11. #11
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    @Sambo, thanks so much! This is definitely cleaner. And would make maintenance definitely easier. I'd still like to refer to a table, but I will definitely take this until I can find that solution.

    Thanks a million.

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    well, Jacc's looks like it solves it for you as tested and adjusting from A12 B12 to A3 and B3 then activated with ctrl/shift/enter. I'd go with that one as it looks like you'll need fewer adjustments.

  13. #13
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    @Sambo, Again, thanks!!

  14. #14
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    @Jacc, this is beautiful!!! Thank you so much!

  15. #15
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    Maybe I misunderstood something. My formula in the attached workbook is in C12.
    In C12 it sums only the values who's account matches for instance the value in A12 (via the mapping table) and who's department can be found in cell B12.

    Edit: Oh, it worked for you, how nice!
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    Right. But your reply was to put the formula in C3, not in C12. At any rate I've got it doing exactly as you were wanting me to do and it works perfectly. THANK YOU SO MUCH!

  17. #17
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping


    You are welcome!

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    Just a small disclaimer, it only works as long as all accountnumbers have the same number of digits (in this case 2 diigits). The day someone decides to mix 2-digit account numbers and 3-digit accountnumbers it may find false matches and fail miserably. Hopefully this is not an issue for you.
    Last edited by Jacc; 04-25-2019 at 02:11 PM.

  19. #19
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    ummmmm....well maybe that's why i'm having an issue. lol

    The result i'm getting for the first cost type is well below what it should be. All departments are 2 digits, except for one. It is department 5. I could make it 05, but that might require some further editing. At any rate, the line I'm working on currently is only departments 20 and 45. I'm not sure if department 5 would be messing that up.


    Not sure if it will help any, but this is the formula I've entered.

    Please Login or Register  to view this content.

  20. #20
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    The challenge is that the account numbers are in the same cell, hence I have to use the SEARCH function. SEARCH will just look for the character 5, it doesn't care if that 5 is alone or if it's part of 45.

    I don't see a smooth solution to that. Possibly have the account cell both start and end with commas like ",21,10," instead of just "21,10". That way i can make the search for ",5," instead of just "5". I think it will work but it will look awkward. Interested?

  21. #21
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    Re: Account & Dept Mapping

    I think you mean the department numbers are in the same cell.

    Should I be able to get around this issue by changing all department 5 entries to be department 05?

  22. #22
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    Yes you should but you have to change the format to text obviously. Or just add an ' in front of the 05. This sign becomes invisible and works the same as formatting as text.

  23. #23
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215
    Iíll try to take a look at it again tonight or tomorrow morning. I tried changing the data to Ď05 but that still gave me the wrong value. In fact for that particular line, Ď05 wasnít even one of the cost centers. So Iím not sure why it wasnít adding them up correctly...

  24. #24
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Account & Dept Mapping

    Wow, 20 000 rows. How long does it take to calculate?

  25. #25
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    I thought Jacc's solution was the best but now wondering if it is starting to look like maybe the sumproduct or sum/sumifs solution might be better?

  26. #26
    Forum Contributor
    Join Date
    04-27-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    215

    [SOLVED] -- Re: Account & Dept Mapping

    I'm still not able to get the formula to work properly for some reason. BUT, when I got to work this morning, the light bulb over my hear turned on and the solution was obvious. What I did instead was create the two tables that I needed on a separate sheet. And on the data source, I just did a vlookup and set the account numbers to the proper cost type and the departments to the proper department group. Then on my output tab, I just used sumifs for the cost type and department group that i needed. Worked perfectly. I don't know why I didn't think of this yesterday.

    Thank you both for all your help!
    Last edited by mvparker79; 04-26-2019 at 01:37 PM. Reason: Solved

  27. #27
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Account & Dept Mapping

    You're welcome for what I did. Sometimes it is best to just rearrange the data to make the process work better. Glad you figured out a way to accomplish what you needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need data from one Excel Sheet separated by 'Dept' and saved as new files with 'Dept' name
    By HospitalAccountant in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 12-06-2018, 12:10 PM
  2. Monthly Dept counts
    By sam1105 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-05-2016, 08:52 AM
  3. Bottom Values for any given dept
    By coda1395 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-09-2013, 12:47 PM
  4. Replies: 1
    Last Post: 09-07-2012, 12:00 PM
  5. Replies: 9
    Last Post: 03-12-2012, 05:30 AM
  6. VBA code for raw data + account code + dept id
    By mingali in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-06-2010, 07:29 PM
  7. Find name in list and get dept # enter dept # on work sheet
    By Jamba in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2006, 06:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1