Standardized formulae creation

[Villalobos]

Hello Chandoo.org,

Please, first of all let me wish to you Happy New Year!

I would like to change a formulae so that it can handle an unlimited unique values. Currently, the formulae can handle only five unique values. This is that what I have in column C (column C means = Remaining stock in the warehouse the after daily outbound deliveries):
{=IFERROR(OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(OR(D2="C",D2="E"),0,1)),IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,A2-E2)))}

My target is that to create a standardized formulae which is not depending on the unique values (on Sheet1), this means that somehow should standardize (or eliminate) the unique criterias which are in double quotes (IF(OR(D2="C",D2="E"),0,1)),IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,A2-E2))) in the second part of the formulae.

Is it possible or not? If it is not possible then can someone recommend an alternative solution instead of the above mentioned formulae? I am complete stuck.

The sample file has been attached.



Thank you for your help!

 

[Luke M]

Can you elaborate on how you columns work? It looks like col A is the inbound, which would increase number in col C. How do col B, E, and F work? My guess is that based on what the code is, determines which column to subtract. If this guess is correct, I would suggest building a lookup table somewhere with the codes and appropriate column number so you can do something like:
=SUMIF(D:D,D2,A:A)-SUMIF(D:D,D2,INDEX(B:E,,LOOKUP(D2,LookupTable)))

And lookup table would look something like:
A 1
B 1
C 1
D 3
E 4

and so on.

 

[Villalobos]

Hello Luke M,

Thank you for the reply. I have attached the modified file to see that how does it works the other columns (A, B and F), the data of column D and E directly come from my ERP system as a simple report therefore there are not formulae behind.

Please, could you explain the lookup table? How do you mean the A 1, B 1, C 1, D 3, and E 4?

 

[NARAYANK991]

Hi ,

I have not seen your modified file ; based on your first uploaded file , will this work ?

Narayan

 

[Villalobos]

Hello Narayan,

Thank you for your attention!
Please, receive my congratulations to +6.000 post, nice job, well done. I wish you much success.

In the second file I just placed the formulaes (in column A, B and F), this was the modification.
Your formulae is exactly do that what I want in the test file, but I have to do some test before I use it in the real time, I think there won't be any problem.

Thank you again!

 

[NARAYANK991]

Hi ,

Thanks for your kind words. A belated Happy New Year to you.

In case you find problems please let me know.

Narayan