So if a code (column M) is entered for the same customer (column K), Id like the most recent date entered (column F) highlighted. Good morning, I would like help creating a macro that finds duplicates in two different columns, then highlights the most recent date entered. In Excel 2007 and later versions of Excel, select the Developer tab, and then select Macros in the Code group. Find duplicates and highlight newest date entered. Actually, You just need to combine Conditional formatting.I used Conditional formatting previously but this only highlighted to Category code I have used MATCH but this only gave a TRUE or FLASE result. ' Loop through each cell in the selection and compare it toĮnter the following data as an example (leave column B empty): I have two similar spreadsheets/data ranges that I would like to highlight the duplicates in column F:I. ' or worksheet, use the following syntax. ' NOTE: If the compare range is located on another workbook Depending on your data and requirement, the most commonly used methods are the conditional formatting feature or the COUNTIF formula to find and highlight the duplicates for a specific number of occurences.
' Set CompareRange equal to the range to which you will How to Find Duplicates in Excel In MS Excel, the duplicate values can be found and removed from a data set.
When the information is on two worksheets, you have.
Press ALT+F11 to start the Visual Basic editor.Įnter the following code in a module sheet:ĭim CompareRange As Variant, x As Variant, y As Variant Finding duplicates with Conditional Formatting in Excel is easy when the data is on the same worksheet. Duplicates in a column can also be found using Excel formulas. The image above shows the built-in tool highlighting duplicate items. There is a built-in function in Excel that lets you highlight duplicates, however, that won't work properly if you have filtered the data. To use a Visual Basic macro to compare the data in two columns, use the steps in the following example: To copy the unique values, select the data set and use the Alt + keyboard shortcut to select only the visible rows, then use the Ctrl + C shortcut to copy the visible rows and paste them wherever required. The Excel Table has been filtered to show only records for January 2012. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Microsoft support engineers can help explain the functionality of a particular procedure. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. It doesn’t matter whether the table contains hundreds or thousands of entries. Excel will immediately look for and highlights all duplicates in a table of any size. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. Click on Highlight Cells Rules and select Duplicate Values. Warning: Microsoft provides programming examples for illustration only, without warranty either expressed or implied.