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.
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.
Best Option- Create a new absolute column
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.
Straight off the Data Filter
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.