Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to identify what is not there
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 -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to identify what is not there
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. On Fri, 8 Oct 2004 19:36:56 +0100, Robert wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to identify what is not there
Myrna
Very many thanks Robert In message , Myrna Larson writes 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify the dqy file | Excel Discussion (Misc queries) | |||
Identify and add formula - can it be done? | Excel Worksheet Functions | |||
Identify a hyperlink | Excel Discussion (Misc queries) | |||
how to identify | Excel Worksheet Functions | |||
Identify links | Links and Linking in Excel |