To highlight the teams in the second list that are not in the first list, select the range B1:B20, create a new rule using the formula =COUNTIF(firstList,B1)=0, and set the format to orange fill. Thus, cell A2 contains the formula =COUNTIF(secondList,A2)=0, cell A3 =COUNTIF(secondList,A3)=0, etc.Ĩ. Excel automatically copies the formula to the other cells. Always write the formula for the upper-left cell in the selected range (A1:A18). As a result, Excel fills the cell with a blue background color. If COUNTIF(secondList,A1) = 0, the team in cell A1 is not in the second list.
Miami Dolphins and Tennessee Titans are not in the second list.Įxplanation: = COUNTIF(secondList,A1) counts the number of teams in secondList that are equal to the team in cell A1. Enter the formula = COUNTIF(secondList,A1)=0ħ.
Select 'Use a formula to determine which cells to format'.Ħ. On the Home tab, in the Styles group, click Conditional Formatting.ĥ. SEE: 30 things you should never do in Microsoft Office (free TechRepublic PDF) A dynamic validation list A validation list lets you limit users to specific values. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you.
#Compare two lists for matches excel 2016 mac code
First, select the range A1:A18 and name it firstList, select the range B1:B20 and name it secondList.ģ. Compare Two Lists For Matches Excel 2016 Free TechRepublic PDF. Compare Two Lists For Matches Excel 2016 Code To Compare In case we have similar requirement but the number of columns are more than two then we need to tweak the code in order to get the result. To highlight the teams in the first list that are not in the second list, execute the following steps.ġ.
0 kommentar(er)
