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

[Keerthi@21]

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.

[Aussiebear]

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?

[Keerthi@21]

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)

[Keerthi@21]

Hi Folks,

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

[Aussiebear]

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.

[Aussiebear]

is there an issue with my solution?

[Keerthi@21]

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.

[Aussiebear]

Have a look at the latest Keerthi@21.xlsm file and see if that works for you.

[Keerthi@21]

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!