I'm using Excel 2016 on my Mac (v10.11) and currently working on a table for the accounting of my company. It contains two sheets: On the first sheet I list up some costs and give them a projectnr.
These costs get copied and pasted on the second sheet. The projectnrs get copied in a protected column, where I need to delete all duplicates. I want to automate this process.
If you click on a button on the first page, the rest of the game will be done by itself. For this, I wrote a VBA function like this: Option Explicit Sub Copy ThisWorkbook.Worksheets('FirstTable').Range('R21:R84').Copy ThisWorkbook.Worksheets('SecondTable').Cells(3, 26).PasteSpecial xlPasteValues ThisWorkbook.Worksheets('SecondTable').Activate ThisWorkbook.Worksheets('SecondTable').Range('Z:Z').RemoveDuplicates Columns:=1, Header:=xlYes ThisWorkbook.Worksheets('FirstTable').Activate ActiveWorkbook.Save Call Print End Sub The problem is that the method RemoveDuplicates does not work on mac! I've tested it on windows and everything works well. Does anyone know what the reason for this could be? If I mark the column on my own and click on remove duplicates in the data tab, it works, but not with this macro.
Introduction: Remove Duplicate Values in Excel for Mac Sometimes your Excel spreadsheet contains duplicate data. Excel offers a feature that will scan for and remove duplicated values. The Ultimate Guide to Find and Remove Duplicates in Excel. Home >> Blog >> Excel Tips >> The Ultimate Guide to Find and Remove Duplicates in Excel; Watch Video – How to Find and Remove Duplicates in Excel.
=COUNTIF( A:A, A2 ) This function counts the number of occurrences of the value in cell A2 (the name 'Ann SMITH') within column A of the spreadsheet. As the Countif function is copied down column B of the spreadsheet, it will count the number of occurrences of the names in cells A3, A4, etc. It is seen that the Countif function returns the value 1 for most rows, showing that there is just one occurrence of the names in cells A2, A3, etc. However, when it comes to the name 'Laura CARTER', (which is present in cells A4 and A7), the function returns the value 2, showing that there are two occurrences of this name.