➤ From the Developer tab, select Macros, a dialogue box will open. ➤ Now select the range of cells from List 1 that need to be inspected for matches in List 2. ➤ Close the VBA window or press Alt+F11 again to return to the Excel workbook. ➤ Click on the Run button or press F5 to activate the subroutine. If data1 = data2 Then data2.Offset(0, 1) = data1
➤ In the editor window, copy the following codes: Sub Duplicates() A new module for the VBA editor will appear where you’ll type the codes. We are going to show the duplicates in Column D with the help of VBScript.
#Excel find duplicates but not blanks code
If you love to code with VBA Editor for Excel functions, then this method might be suitable for you. Using VBA Editor to Find Duplicates in Two Columns The matches will be shown as TRUE, and the mismatched result will return as FALSE logical value.Ħ.
➤ Press Enter, autofill the rest of the cells by using Fill Down & you’ll get all the exact matches with case sensitive on. ➤ In Cell D5, we have to type the following formula: We can use IF & COUNTIF functions together to find data from the 1st column in the 2nd column for matches. Like the picture below, you’ll see all the matches with the selected & highlighted colors.Ģ.2 Combining IF & COUNTIF Functions to Detect Duplicates in Two Columns ➤ Select the color you want to show for highlighting the duplicates. ➤ Under the Home tab & from the Conditional Formatting drop-down, select Duplicate Values from the Highlight Cells Rules. To find duplicates in two columns in any rows, you can directly use the related command in Conditional Formatting for highlighting. Finding Duplicates within Any Rows in Two Columns 2.1 Applying Conditional Formatting to Locate All Duplicates in Two Columns In the picture below, the matches in the same rows are now visible with the selected color.Ģ. ➤ Press OK for the last time & you’re done. ➤ Press OK & you’ll be shown the Sample format with selected color in the New Formatting Rule dialogue box. ➤ From the Fill tab, select a color that you want to use for highlighting the duplicates. ➤ Select Format option & another dialogue box will appear.
➤ In the Rule Description editor, type =$B5=$C5 ➤ Now select the rule type to use a formula to determine within cells to format. ➤ Under the Home tab, from the drop-down of Conditional Formatting in the Styles group of commands, select the New Rule command. ➤ Select the whole range of cells that is considered for finding duplicates. If you want to find duplicates within the same rows in two columns then probably conditional formatting suits best to highlight the matches and it won’t let you type any function to search for matches. ➤ Use Fill Handle to autofill other cells in Column D & you’re done.ġ.3 Applying Conditional Formatting to Highlight Matches within Same Rows in Two Columns
0 kommentar(er)
