Spreadsheets are great at sorting and identifying numbers but what can you do to match positive and negative numbers in excel. If you sort the numbers the ‘matches’ are as far apart as could be possible.
Table of contents
YouTube Match positive and negative numbers
Matching numbers that have different signs
As shown below we have a list of numbers but they don’t have unique reference numbers. So we need to match them based on the actual amount, and to add to the complexity the ‘match’ will always be the opposite sign, so positive to negative matching.
Best Option- Absolute column to make numbers positive
We would add a new column that needs to show the amount in column C only as a positive number. To do this there is a function called ABS (absolute) which is very easy in that you just need to point at the cell i.e. =ABS(C2).
The benefit of this is that you have a column to work with that can genuinely be matched by sorting, but you still have the original data.
So if we now sort based on column D (largest to smallest), you suddenly have similar numbers close together. As shown below it looks like the first 2 rows are an easy match. But then we have 6 items that have the same number. Which belongs to which will need to be investigated (Excel is good but not that good), but note that if you add all these up it comes to zero which is a good sign.
Out of interest note that we also have another column in column E called Matched. We use this to note which rows we are happy with (e.g. row 2 and 3 would say yes as it matches) so that eventually we are left with only the problems.
Use Data Filter to pick the amounts
If you switch on your Data Filter you can achieve something similar. Note below that when we click on the drop down arrow it shows the list of numbers in order (so not useful yet).
To find the matches, in the Search Box type a number you want to investigate. As shown below if we type 18 962 (the space is important) it only shows the items that CONTAIN that number and they are both ticked.
If you click OK now it will show only the items that contain that number as shown below. You can now decide whether they are matching or not.
Note that this method is a lot more tedious and can be problemsome if you have decimals that perhaps don’t match exactly.
Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Excel negative numbers in brackets
Identify positive and negative numbers in excel
Extract text from Excel cell AFTER an instance of a character