To highlight the duplicate values, Select the cells containing data > in the Home tab of the Excel Ribbon > Conditional Formatting > Highlight Cells Rules > Duplicate Values…Ī dialogue called Duplicate Values will be activated, Click on OK and all those Text and Number data with multiple instances will get highlighted. =IF(COUNTIF($B$3:$B$15,B3)>1,"Duplicate","") Conditional Formatting to highlight duplicatesĬonditional formatting can be used to highlight the Duplicate values as well as the Unique values in a dataset. Then the IF function is used to check whether the ‘count’ is greater than 1 or not. And then VLOOKUP-CHOOSE or INDEX-MATCH function to trace down the names. ![]() ![]() Like what we did in the above formula, here also we will use the COUNTIF function to find out how many times each value is repeated in the dataset. Apparently I can use the LARGE function, to get top values. If you want to remove the duplicates from duplicates, wrap the above formula with the UNIQUE function =UNIQUE(FILTER(B3:B15,COUNTIF(B3:B15,B3:B15)>1)) IF and COUNTIF Function to spot the duplicates The matching numbers will be put next to the first column, as illustrated here: A. The duplicate numbers are displayed in column B. ![]() Then the FILTER function is used filter out those values with a count greater than 1.Ĭombining FILTER and COUNTIF function to return the duplicate values in the data range B3:B15 =FILTER(B3:B15,COUNTIF(B3:B15,B3:B15)>1) Recommended Articles How to Return Multiple Values using Vlookup Function As we have told above, VLOOKUP works for unique values. To do this, select File > Options > Customize Ribbon, and then select the Developer tab in the customization box on the right-side. In this formula, we will use the COUNTIF function to find out how many times each value is repeated.
0 Comments
Leave a Reply. |