Consulting

Results 1 to 9 of 9

Thread: RE: Excel Formula to update Account number based on signage using mapping key

  1. #1

    RE: Excel Formula to update Account number based on signage using mapping key

    Hi Folks,

    I would need your dearest help to get an excel formula for the logic as mentioned below:

    1) I have an sheet named "Pivot" which contains item details along with its amount related to sales data. What i exactly need is - Excel formula to be updated for the logic-> Wherever negative sign is available in the Units Cost column, mapping key need to be changed accordingly. (example – mapping key for account number "4016000" should be updated as“40” and "607412" should be “50”)

    2)If the amount value is positive in Units cost column, then the mapping key needs to be change vise versa ( example - mapping key for account number "607412" should be updated as“40” and "4016000" should be “50”)

    Attached the snapshot of pivot data for reference.snap.jpg


    Kindly let me know if any other details required. Thanks for your help in advance.

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,178
    Location
    Quote Originally Posted by Keerthi@21 View Post
    1) I have an sheet named "Pivot" which contains item details along with its amount related to sales data. What i exactly need is - Excel formula to be updated for the logic-> Wherever negative sign is available in the Units Cost column, mapping key need to be changed accordingly. (example – mapping key for account number "4016000" should be updated as“40” and "607412" should be “50"
    Where does the mapping key find account number "4016000" or "607412"? In fact where does this reside given that you have provided a snapshot rather meaningless data?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Hi Aussiebear,

    Disregard the snapshot attached in initial thread. I have attached the excel sheet for reference.

    For easy understanding i have highlighted in yellow color where i need the help for below mentioned points:

    Default logic for mapping key for account (40,50):

    607412-40
    4016000- 50

    Based on signage account number needs to be swapped. Detailed explanation below:

    1) I have an sheet named "Pivot" which contains item details along with its amount related to sales data. What i exactly need is - Excel formula to be updated for the logic-> Wherever negative sign is available in the sum of total amount column, account number need to be changed accordingly. (example – if amt is negative, account number "4016000" should be under “account 40” and acc num "607412" should be “Account 50” column)

    2)If the amount value is positive in sum of total amount column, then the mapping key needs to be change vise versa ( if amt is Positive, account number "607412" should be under “account 40” and acc num "4016000" should be “Account 50” column)
    Attached Files Attached Files

  4. #4
    Hi Folks,

    Please share your comments/suggestions regarding this thread. Awaiting your response.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,178
    Location
    In using the second of Book2.xlsx, I created a simple Lookup table, named it as tblLookup. Then in cell C2 entered =If(B2<0,40,50) and in Cell D2 =Vlookup( C2,tblLookup,2) and filled down.
    Attached Files Attached Files
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,178
    Location
    is there an issue with my solution?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Hello,

    I checked your logic, it is not correct for my requirement. Also, it is available only for one account number 40.

    I would need both account numbers to be swapped and also update mapping key automatically. I tried out my own logic, for each sum amount (same value) account numbers gets swapped but mapping key is same (highlighted in orange color in the snapshot)

    example:

    Sum Amount - Mapping Key - Account Number
    (12,588.35) - 50 - 4016000

    (12,588.35) - 40 - 607412

    mapping key.jpg

    I am working on it. Let me know if you could help here. Also, Please help me out on the other thread which you responded.

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,178
    Location
    Have a look at the latest Keerthi@21.xlsm file and see if that works for you.
    Attached Files Attached Files
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Hello,

    This works fine similar to my requirement. Hence, this thread can be closed.

    Could you please help me on the other thread with Title:

    RE: Insert/Embed outlook mail using vba Excel



    Thanks for your help in Advance!

Posting Permissions

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