Trying to identify what is not there

[Robert]

I have a 1600 row list which includes in column A group references e.g.
C2. In column B there are option references e.g. DHF, which have been
selected from a list of 15 options. The number of entries (rows) for
each group reference is variable.

Example:

Column A Column B
Group ref Options shown
C2 DHF
C2 ECN
C2 ENV
C2 FOR
D2 ECN
D2 FOR
D2 HOR
D2 NEC
D2 OIL
D2 OTH
D2 PAS

Complete list of options;
DHF
ECS
ECN
ENV
FOR
HOR
NEC
NFC
NUT
OIL
OSA
OTH
PAS
PRO
TGR

What I am trying to achieve is a list which shows the options for each
group reference that are *not* shown in column B.

Example:

Column A Column B Options not shown
C2 DHF
C2 ECN
C2 FOR ECS ENV HOR NEC NFC NUT OIL OSA
C2 OTH PAS PRO TGR
D2 ECN
D2 FOR
D2 HOR
D2 NEC
D2 OIL
D2 OTH
D2 PAS DHF ECS ENV NFC NUT OSA PRO TGR

Grateful for any suggestions

 

[Myrna Larson]

Have you considered a pivot table: column A as the row field, column B as the
column field, and column B (again) as the data field? That will give you a
rectangular table with the missing options having a count of 0.

If you need a "list" as you show, you should be able to construct a formula to
concatenate the Options (from the table header row) into a string, selecting
only those where the count is 0.

 

[Robert]

Myrna

Very many thanks

Robert