Excel - Check Value Within a Set of Numbers and then use Conditional Formula

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.

Interconnectivity issues between Idea 3G and Vodafone

This is a post about an interesting issue that I was facing since last 3 months or so. This involved interconnectivity issues between Idea and Vodafone. I wrote several complaints to the customer care teams of both the service providers, separately and jointly as well. No solution was found and both the service providers were saying that the problem is with the other network. But at last, I had to identify the problem on my own. The problem is surely from Idea's end. Now that the problem is identified, I have informed Idea Customer Care and now its up to them to fix it. 

I am having two postpaid mobile connections. One is from Idea Maharashtra which is used by my wife and another one is from Vodafone Maharashtra that is used by me. As an example in this post, lets assume that Idea numbers are in series I1, I2, I3 and so on and similarly Vodafone series is V1, V2, V3 and so on. 

The problem that I noticed first was not receiving incoming SMS on Idea number from Vodafone number. So my wife was not getting any SMS from me. This means that I1 was not getting incoming SMS from V1, V2, V3 etc.

But V1, V2 and V3 were able to send SMS to I2, I3 etc. So it was logical that the problem was with I1 i.e. Idea number and hence complaint was lodged with Idea. They checked everything, refreshed the services and then informed me that the problem might be from Vodafone's end. 

Then the complaint was lodged with Vodafone using number V1. They also checked everything. During the calls from network team, they put the number V1 on server for testing and asked me to send SMS to I1. That SMS was not hitting Vodafone's server at all. So the confusion increased and I guessed that the problem might be from Vodafone, since the SMS is not hitting their server. So I kept on following it up with Vodafone. The issue remained unresolved from both the service providers. 

Then one interesting thing happened. My wife travelled out of Nagpur for a couple of days. As soon as she left Nagpur, she started receiving SMS on her Idea number I1 from my Vodafone number V1 and from V2 and V3 as well. It was then the problem got clicked in my mind. In Nagpur, she was always on Idea 3G network. But outside Nagpur, I1 got switched to 2G network and she started receiving messages. When she came back to Nagpur, I tested the same to reconfirm by putting her cell on UMTS (3G) and GSM (2G) modes. So the problem now identified is that Idea 3G number is not receiving any incoming SMS from Vodafone numbers (on 3G and 2G as well). 

I have informed the Idea Customer Care team with these details and hoping them to fix the issue, since the problem is identified now. 

Another interconnectivity issue is there between Idea 3G and Vodafone 3G. The above mentioned Idea number I1 is unable to receive video calls from Vodafone number V1 and so on. Video Calls are possible from I1 to V1, but not from V1 to I1. I had complained the same with Vodafone. Their network department called me and the executive gave me his mobile number (say V4) and Video call was successful from V1 to V4. So again the logical interpretation is that there are some issues of interconnectivity between Idea 3 and Vodafone and they are from Idea's end. Idea 3G is somehow blocking incoming SMS and incoming Video Calls from Vodafone. 

Both the issues of incoming SMS and Video Calls are reported to Idea for fixing it and to Vodafone for their information.