Yesterday, one of my friends called me and asked for a help in Excel. Basically he had a dump exported in Excel from SAP finance module and in that dump, there were more than 1,50,000 line items. In SAP, all debit amounts are positive (+) figures and all credit amounts are negative (-) figures. But the problem my friend faced was that when he exported them into Excel, all the figures were positive figures only, but each figure was associated with a posting key, which was a determining factor to decide whether it was debit or credit i.e. whether (+) or (-).
And those determining posting keys were in combinations of (+,-) like (40,50), (89,99) etc depending on transaction type. There were 3-4 pairs, he had at present, but in future that number was going to increase. He wanted an excel formula to change the amount to either (+) or (-) depending on the posting key.
I started to look for the possible ways to do it. I tried to use the "IF" function in combination with excel functions like "MATCH", "VLOOKUP", "LOOKUP", "OR", but wasn't getting the desired results. And since the number of pairs were uncertain, I intended to give him a future ready solution.
Finally I was sucessful in acheiving the desired results using "IF" and "COUNTIF" functions. The solution was as under with imaginary figures and imaginary sets of posting keys.
Please feel free to export the above sheet into Excel and look for the formula used in Column F. You may also click on any cell in Column F above, but the formula used won't be visible completely, unless you scroll down in formula field. If you accidentally edit the document, just reload the page and the original document would be visible again.
Even if the combinations increase in future, the codes need to be added in the columns A and B and range needs to be extended in the formula.
Any better solution is always welcome. Its a constant learning process.
tussi great ho bhai...
ReplyDelete