In my case it was column 'A' IF( A9='','',IF( A9= A10,'',1))ĭon't forget when you try it on your sheet ,to drag the handle on the bottom right hand corner of the cell you have entered the formula in, right down to the last row that has an account number in it. Just make sure the column you reference for the account numbers, is the column that has the account numbers. You can use any spare columns for the formulas. You'll have to change the cell references (A1,B2,C3 etc) to suit your sheet. If you want to put it all back in the original order, do the same selection process again & sort column K into ascending order.

I'm not sure if that part will carry through from Calc to Excel. Makes it easier to find the duplicates when scrolling down. I also conditional formatted column A to show duplicate cells with a red background after they've been sorted into numerical order. Click E9,G9,E7,G7 or I7 to see the formulas in the formula bar. Cell I7 shows the total number of duplicates. Column G shows all of the account number entries in Column A including the duplicates. You can customize the status bar to show when Scroll Lock is on. If you’re experiencing the scrolling behavior and you don’t see Scroll Lock on the status bar, it’s not selected to display. Notice on the above image that Scroll Lock displays in the status bar in Excel.

Column E shows the total of all the account number entries in column A, minus the duplicates. RELATED: How to Customize and Use the Status Bar in Excel.

That will group all the duplicated account numbers together. Go to 'Sort' wherever that is in Excel & sort column A into ascending order. You do this so when you sort, it moves all the cells in the rows at the same time & you don't end up with a jumbled up mess, whilst leaving the totals at the top, where you can see them. Select the whole sheet, then unselect rows 1-8. No macros are used.īefore you do anything & as a backup, save the file you're playing with to somewhere else, just in case. =IF+A1:A420(A1='','',IF(A1=A2,'',1)) Will never workĭownload the file from It shows how it works.

Excel skips rows when scrolling