RE: Excel formula/VBA code to automatically update the mapping key

[Keerthi03]

Hi Folks,

Based on signage mapping key needs to be updated. I have updated the excel file sample for your reference.



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 amount is negative, mapping key should be 50 for account number "607412" and mapping key should be 40 for account number "4016000".

2) If the amount value is positive in sum of total amount column, then the mapping key needs to be change vise versa -> mapping key should be 40 for account number "607412" and mapping key should be 50 for account number "4016000".

Note: I will paste the same data for each code number based on last row one by one, so we need to implement the logic accordingly for mapping key.

Example - final output to be in below format for single amount value:
Sum Amount - Mapping Key - Account Number
(12,588.35) - 40 - 4016000
(12,588.35) - 50 - 607412

 

[portews]

If you can specify the negative mapping key, as well as the positive for each account, it's very easy.

Put this in the Mapping Key column

=OFFSET($K$1,MATCH(C2,$K$1:$K$3,0)-1,IF(B2>0,1,2))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: RE: Excel Formula to update Account number based on signage using mapping key
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

Do you even need a mapping table ?
That looks like SAP data to me. Typically the mapping works in Account no ranges.
Will something like this work for you.

20220626 SAP Data Posting Key Mapping Keerthi03.xlsx
	A	B	C	D
1	Code	Sum or Total	Acct No	Posting Key
2	1324	(12,588.35)	607412	50
3	1324	8,646.28	607412	40
4	1324	194.82	607412	40
5	1324	258.94	607412	40
6	1324	(580.66)	607412	50
7	1324	(12,588.35)	4016000	40
8	1324	8,646.28	4016000	50
9	1324	194.82	4016000	50
10	1324	258.94	4016000	50
11	1324	(580.66)	4016000	40
12	1324	502.03	607412	40
13	1324	(772.16)	607412	50
14	1324	(1,671.61)	607412	50
15	1324	502.03	4016000	50
16	1324	(772.16)	4016000	40
17	1324	(1,671.61)	4016000	40
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	=IF(LEN(C2)=6,IF(B2<0,50,40),IF(B2<0,40,50))

 

[Keerthi03]

Hi All,

Received solution. I have tried out all combinations and adjusted according to my requirement and it works fine.

@Fluff - Sure, I will make it clear while posting new thread, if any cross posting done, then the same details will be added in my thread going forward.

Thanks all for your help

 

[Alex Blakenburg]

Thanks for letting us know, glad we would help.