![]() ![]() ![]() If any value from List A is missing from List B, a red TRUE will appear next to that value in List A’s “Missing?” column. Repeat steps four through six for cell E5, making sure to copy the formula down the entire List B.In cell E5, enter the formula: =ISNA(MATCH(D5,A:A,0)) and press Enter.Select cell B5 down to the end of List A and press Ctrl+D to copy the formula and conditional formatting down the entire List A.In the Text That Contains dialogue box, type TRUE and click OK.Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…. ![]() In cell B5 enter the formula: =ISNA(MATCH(A5,D:D,0)) and press Enter.Label the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”.I chose column A for List A and column D for List B. Arrange the lists in columns, leaving at least two blank columns in between.To do this we’ll write a formula using the ISNA and MATCH functions. We want to know if any account values from List A are missing from List B, and vice versa. In the example below, we have two large lists of four digit account values: List A and List B. Which new clients need to be added to the master database? Which credit card receipts are not reflected on the monthly statement, and vice versa? What checks and deposits are still outstanding? It’s a tedious task if you do it manually. For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |