Ask Your Question
0

How do I see what's missing in a list? [closed]

asked 2015-10-10 06:45:16 +0100

aristotle2600 gravatar image

Let's say I have a list of text items in A1:A5. Then in B1:B5, I have another list, of items from the first list. I would like to scan through the B list, and see what if any items are missing. For example, let's say that my A column has

Apple
Banana
Cherry
Date
Elderberry

Then in the B column I might have

Banana
Elderberry
Cherry
Date
Cherry

I already know how to alert on the fact that Cherry appears twice, but I also want a cell to show the missing entry, Apple. How do you do this?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-18 03:35:58.900344

1 Answer

Sort by » oldest newest most voted
0

answered 2015-10-10 08:26:35 +0100

pierre-yves samyn gravatar image

HI

Two techniques are illustrated in the Missing.ods attached.

  1. Use e.g. =IF(COUNTIF(B2:B6;A2:A6);"";A2:A6) to show what is missing (matrix formula, so validation with Ctrl+Shift+Enter)
  2. Conditional Formatting for A2:A6, formula ISNA(MATCH(A2;$B$2:$B$6;0))

Regards

edit flag offensive delete link more

Comments

Thanks; I've seen matrix formulas used in solutions from time to time, but I don't know really what they are. Where might I learn more about them? Just in the help somewhere?

aristotle2600 gravatar imagearistotle2600 ( 2015-10-10 09:34:42 +0100 )edit

Definition, syntax & examples here

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-10-10 11:31:53 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2015-10-10 06:45:16 +0100

Seen: 55 times

Last updated: Oct 10 '15