"Ghost numbers"

Question

Below is a chart of interface control numbers.

7	0	0	4	3	9	5
1	4	8	8	1	7	1
1	4	8	8	5	3	7
7	6	8	0	5	3	7
6	13	13	9	10	4	8
10	4	2	6	8	12	8
6	2		8	4	4	6
1	4	8	0	1	3	1

Looking at this, there are 7 columns.  What I need is a way to find Ghost numbers.  A ghost number is a number that works in the interface system but was not designed to, it shows up when the columns, which require certain criteria are calculated.  Column 5 is a good example.  The interface control numbers results would be, in this case, 4,1 (the bottom two rows).  The criteria used as the interface are 1,5,5 (bottom 3 rows of top section).  For this example, 1,5,&5 are designed to work in the system.  However, when we look at the compiling of the control numbers column we see a number 4, in this case (4+1=5).  This 4 is called a ghost and will access the system but because of the criteria cannot be changed for calculations (it is mechanical) knowing there is a 4 ghost becomes the best solution to preventing uncontrolled interface access--we can do this by simply not giving out any #4 for use in the 5th column for access.  MY QUESTION, how do I set-up a formula to find these ghost numbers in a spreadsheet.  The ghost numbers are only found in the bottom two rows (this example shows that the last three columns have ghost, 4, 4, & 6) of the interface control numbers, here they would be

6, 2, -, 8, 4, 4, 6

1, 4, 8, 0, 1, 3, 1,

Thanks

Answer 1

Hello,

please explain the logic that identifies a ghost number. From your description it sounds as if

• if the sum of the last two numbers in any given column is present in either rows 2, 3 or 4 of that column, then the two are ghost numbers. 

Does that work? If so, don't all columns contain ghost numbers?

Column 1:  6+1 = 7 and 7 is in row 4

Column 2:  2+4 = 6 and 6 is in row 4

Column 3:  nothing +8 = 8, in rows 2 - 4

Column 4:  8+0 = 8, which is in rows 3 and 4

etc. 

A formula that can identify them would be

=IF(ISNUMBER(MATCH(SUM(A8:A9),A2:A4,0)),"ghost","")

If that is NOT how ghost numbers are identified, can you please explain the logic that DOES?

Answer 2

The situation is having 380-440 pages of these control numbers listed in columns as you saw.  Without physically looking at maybe 440 pages of these numbers, I was thinking if I could run an inquiry for each section to find the ghost numbers I could alleviate two hours of work to find the ghost every time I have to reset interface numbers.  If that doesn't help I will have to rethink my question from another perspective

Unless I'm mistaken, the issue is not so much one of perspective; rather one of logic.

Although you probably feel you have done your best to explain the problem in hand, from an objective point of view there is little in your original post in the way of logically-formulated criteria or conditions, such as we might hope for as a basis for developing potential solutions in Excel. 

Regards

Answer 3

But can you explain how a ghost number can be detected? I only see figures...

Answer 4

The situation is having 380-440 pages of these control numbers listed in columns as you saw.  Without physically looking at maybe 440 pages of these numbers, I was thinking if I could run an inquiry for each section to find the ghost numbers I could alleviate two hours of work to find the ghost every time I have to reset interface numbers.  If that doesn't help I will have to rethink my question from another perspective

Answer 5

Sorry,

I do not understand the question, we need more info.

Is there no better way to avoid these ghosts?