I have an excel file that includes thousands of line items (consisting of both positive and negative numbers) getting knocked off with each other and getting sum net to zero.
If this sounds familiar to you then you are at the right place. In this article, I will tell you how to knock off such numbers with each other?
This kind of situation generally arises when your data consists of both the original and reversal entries as well. And, In a large organization (where different people have access to post the entires) this kind of situation is very normal.
For example, suppose you passed an entry debiting General Ledger number ‘1000001’ at $500. But, immediately you realized that you passed the wrong entry and hence reversed the same with crediting $500 to it.
Now, while doing the analysis or preparing the reports, both these entries will be no use for you. You need to remove these entries from your file to work.
One of the ways to remove these kinds of entries is by doing it manually. Find and Delete these kinds of entries manually from your excel file.
The manual method sounds good if you few numbers of line items. But, What if you have thousands or lakhs of rows in your excel files?
In such a case, you need to find an alternative to the manual method.
We will discuss that alternate in this article. This technique will help you to remove the positive and negative numbers getting net to zero automatically.
Trust me, this formula will find lakhs of such entries within a minute.
Remove Offsetting Positive and Negative Amounts in Excel
Let us understand this formula with three kinds of scenarios
- The exact amount is getting knocked off. For example, $500 is getting knocked off at $500 only.
- The exact amount is not getting knocked off. For example, $500 is getting knocked off at $500.10.
- Positive and Negative numbers are in different columns say Debit and Credit column.
- Only selected types of entries needs to be selected.
Note: Please download the attached excel file for better understanding the below tutorial. The attached excel file include real examples with formulas.
Considering the above three cases, let us discuss them one by one below.
#1. The Exact Amount Are Getting Knocked Off
If this is your case then you are lucky. This is the easiest case among the above three.
This method involves four parts.
- Convert Negative numbers into Positive numbers
- Use Count If function to allocate a unique number to the original values
- Use the Concatenate function to combine numbers generated from Step1 with unique numbers generated from Step 2.
- Once again use Count If function to find numbers getting repeated more than twice.
Let us understand this in detail below.
Assuming the values are given in column ‘G’, first, I will use ABS function in column ‘H’. This will convert all the given negative numbers into positive ones. Whereas, the positive numbers will remain positive only.
I am performing this step because for an excel 1000 and -1000 both are different numbers. Now, to find the duplicates, I need to convert the -1000 to 1000.
Next, in column ‘I’, I will use the Count If function. But, this time, I will use it in the reverse method. To know the reason please refer to the below-attached image.
Here the Count If function will be used on original numbers ( including negative). This is because I want to consider both positive and negative numbers as separate units. It will allocate ‘1’ to 1000 and once again allocate ‘1’ to -1000 as well.
Next, I will concatenate both the answers received in Step 1 and 2. I will do this next column that is ‘J’.
At last, I will use the excel function ‘Count If’ and ask excel to find all the entries which are getting repeated more than one time.
This will be done using the below formula.
=IF(COUNTIF(J:J,J3)=2,"Getting Knocked off","Not Getting Knocked Off")
The above formula will highlight all the cases where the numbers are getting repeated twice and getting sum net to zero. This is the only reason why I have used equal to 2 in the formula.
If in case it is repeated more than twice than in that case, the sum total will not net to zero and that will not get knocked off to each other.
#2. The Exact Amount Is Not Getting Knocked Off
Here, by term ‘Exact Amount Is Not Getting Knocked Off’, I mean the sum of numbers is not getting net to zero. Instead, there is a difference of -10 to 10.
That means I need to knock off 1000 with -1000.10 or 1000 with 1006.10.
In such a cases, the above technique will remain the same except one additional column will be added to convert numbers into integers or nearest ten digit. Here, in my case, I have done this by adding column ‘H’.
If the difference amount is ranging between -1 to 1 then TRUNC function will be used. This function will truncate the numbers into Integer. That means it will convert the 100.10 in to 100 and -100.8 into -100.
Whereas, ROUNDUP function will be used when the difference amount is ranging between -10 to 10. This function will convert the numbers to the nearest ten digit. That means it will convert 102.06 into 110 and -101.23 into -110.
Next, the remaining steps will remain same for rest of the process.
#3. The Numbers Are In Different Columns Say Debit and Credit
In such case, you need to arrange the number first into one column and then apply the remaining steps.
Assuming the numbers are G&H Columns of the file, the formula in column I will be.
The above formula will copy the amount from column ‘G’ and in case the column G is empty than it will copy the amount from column ‘H’.
Next, the remaining steps will remain same for the rest of the process.
#4. Only Selected Types Of Entries Need To Be Selected
Assuming , I only want to knock off ‘Invoices’ ( represented by type ‘INV”), I will concatenate the ‘Document Type’ along with other values.
This will ignore other values represented by other document types.
Of course, the rest of the steps will remain same.
Over to You
The over all logic to identify the knock off entries is simple. First, it creates a duplicate numbers by converting the negative into positive. Second, it will allocate unique numbers to both positive and negative numbers as well. Third, it count the number of cases appearing twice in the list.
Next, if in case if you don’t understand the above steps then I suggest to download the below attached excel file for the real examples with formula.
Or else, you can contact us directly through ‘Contact Us’ page or share your message in below comment box.
At last, if you know any other and easy way to find out knocked off entries then please do share with others. You can share your method in below comment box or contact us to include the same in our article. We will be happy to include your suggestion in our article.