+ Reply to Thread
Results 1 to 4 of 4

RE: Excel Formula/VBA code to update Account number based on signage acc. to mapping key

  1. #1
    Registered User
    Join Date
    10-06-2020
    Location
    Chennai
    MS-Off Ver
    2016
    Posts
    9

    RE: Excel Formula/VBA code to update Account number based on signage acc. to mapping key

    Hi All,

    I would need your dearest help to get an excel formula or VBA 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. 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

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,405

    Re: Excel Formula/VBA code to update Account number based on signage acc. to mapping key

    G2=IF($B2<0,$F2,$D2) and drag down

    H2=IF($B2<0,$D2,$F2) and drag down
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-06-2020
    Location
    Chennai
    MS-Off Ver
    2016
    Posts
    9

    Re: Excel Formula/VBA code to update Account number based on signage acc. to mapping key

    Hello,

    Your above logic works fine for account numbers swapping according to sum value sign. I would need mapping key also to be updated automatically.

    For each sum amount account numbers gets swapped acc to sing- postive/negative along with mapping key - refer example format below).

    When i applied your logic, acc numbers gets swapped
    but mapping is same (highlighted in orange color in the snapshot)

    mapping key.PNG

    Example - final output to be in below formst

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

    (12,588.35) - 40 - 607412



    Kindly help out!!
    Last edited by Keers1; 06-25-2022 at 12:09 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,268

    Re: Excel Formula/VBA code to update Account number based on signage acc. to mapping key

    It seems to me that the following formula should work for the Mapping Key, assuming that the Sum of Total Amount (Rounded) is in column B:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Macro to compare sheet1 to Mapping and Extract account numbers that do not exisst
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2022, 03:24 AM
  2. [SOLVED] Signage flip with custom number format in pivot table
    By Gti182 in forum Excel General
    Replies: 2
    Last Post: 06-18-2019, 08:57 AM
  3. Account & Dept Mapping
    By mvparker79 in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 04-26-2019, 01:41 PM
  4. IF Statement that will assign account types based on "account number"
    By aladdin16 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-08-2014, 05:04 PM
  5. Replies: 6
    Last Post: 04-21-2014, 08:57 AM
  6. [SOLVED] How to censor account numbers based on how many characters are in the account number
    By Mcorydon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 01:06 PM
  7. [SOLVED] need Macro code for validation of account number
    By tmprao27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2012, 06:30 AM

Tags for this Thread

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