When working with lists of data you sometimes need to work through the duplicates to decide which you can leave and which can be deleted, or alternatively which need to be investigated for reconciliations. This can be a tedious process involving lots of sorting and unsorting. Below we have shown a way to goto the next duplicate in Excel list using hyperlinks.
As shown below some bank account numbers from a payroll. We need to check why there are duplicate bank account numbers in the payroll. Some might be legitimate in that it is a joint account but some could be phantom employees! If you look closely you will see some duplicates but even in this small list it is hard to spot.
Some steps to follow. Note that we have split it but you can combine it into one formula if you want
Step 1: Where is the next duplicate- the MATCH function
To do this we can use the MATCH function.
MATCH tells you in what position it finds the next match. The trick here though is to only put the dollar signs on the back end of the array. This way, as the formula is copied down it always compares the current bank account number, with all the ones AFTER it. Also, we look for an exact match so that it stops at the first match it finds (keep in mind there could be many more matches)
It will either return a number e.g. 32 or an error message meaning that it couldn’t find another duplicate. The number means that is how many rows you must go down from the current row to find the next duplicate.
Step 2- What Row are we in
To make it neater in column G we have just put an IFERROR in to show a 0 if there are no duplicate or the number of rows down where the next duplicate is.
We now need to know what row we are currently in (you will see why in the next step). The function to use here is the ROW function and all you need to do is point to the cell.
In column I we have just added up the ‘Current Row’ column (H) and the ‘Row of next duplicate’ column (I) to figure out on which row of the sheet the duplicate occurs.
In column J we can make use of the ADDRESS function to convert a row and column into an Excel cell reference. Note that in the formula bar we have linked the ADDRESS function to column I which tells us the row and then typed 13. This is because we want the active cell to be in column 13 (the M column) on the row of the duplicate (you will see why just now)
Step 4- Create a hyperlink that jumps you to the next duplicate
The HYPERLINK function is great for helping with navigation around a spreadsheet. In this case we want to create a link that will jump to the next duplicate of that particular row.
The HYPERLINK function is relatively easy but with one trick. Note that we have refereed to the address column in column J but we appended a ‘#’ to it i.e. “#”&J18 in the example below. If we don’t do this Excel doesn’t know which spreadsheet to look in (hyperlinks typically have the full path). By putting the # in, you are telling it to look in this sheet. At the end of the Hyperlink we put a friendly name we want to appear, in this case ‘Next Duplicate’.
You can stop at this point and start to use the hyperlinks. When you click on a hyperlink it will take you to the next duplicate. If there is no duplicate it will stay where it is.
If you want to go back to the previous link you can hold the ALT key down and press the left arrow key (shortcut for the back function)
Step 5- Making it more useful
Note below in column L we have changed the HYPERLINK formula so that, if there is no more duplicates (as indicated by a zero in column G), then it must say ‘No More’ , otherwise say ‘Next Duplicate’.
We have also added another column M called decision. This is where the hyperlink jumps to (column 13) and you can now enter what your decision is regarding that duplicate e.g. OK, Check, Delete etc. At the end then you can use the FILTER to extract or delete the items you are looking into.