Spreadsheet: 474499d1470770049-array-formulas-limitatins-could-be-pc-memory-size-problem-20160808-ef-multi-data-comparison-using-array-formulas
Sheet: MT - COPIED DATA

FITTING # |  | [NAME] OF ITEM | WEIGHT OFF (Kgs) | WEIGHT ON (Kgs) | CODE CNT | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | ON/OFF kg EQUAL or  NOT EQUAL | Are ALL Weight Entries Equal kg? Or is there a Mis-Match? |  | Fault Examination
1SAMBTSG |  | COULD BE ANYTHING | 9548 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | No Code | No Code
1PAMBTSG |  | COULD BE ANYTHING | -99.5 | -951 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 | TEXT in Kg | [NAME]) have TEXT - not Number
3SBMBTSG |  | COULD BE ANYTHING |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | FIT-WGT NOT Equal | Fitting No. Count does NOT equal No. of Weight Entries
 |  | COULD BE ANYTHING | -61.5 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 | ON-OFF NO Kg | NO kg
4STMBTSG |  | COULD BE ANYTHING | -67.5 | 958 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6 | [NAME] (-) | [NAME] is NOT Negative (-)
4PBMBTSG |  | COULD BE ANYTHING | 0 | 958 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7 | [NAME] (+) | [NAME] is NOT Positive (+)
3PTMBTSG |  | COULD BE ANYTHING | -999 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8 | ON-OFF-CE? | ON & OFF kg Count Error ">1" ∆ (Should not be more than 1) | [NAME] required
2SFMBTSG |  | COULD BE ANYTHING | -41.5 | TEXT |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 | ON-OFF-SL? | ON & OFF kg Same Line (row) | [NAME] required
2PFMBTSG |  | COULD BE ANYTHING | -41.5 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 | ON-OFF kg MM? | ON & OFF [NAME] (Not all entries are equal weight)
4SBMBTSG |  | COULD BE ANYTHING | -759 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | True | All Conditions OK
4PTMNTSG |  | COULD BE ANYTHING | -4587 | 0
3STMBTSG |  | COULD BE ANYTHING | -98 | 0
2APMBTSG |  | COULD BE ANYTHING | -76 | 0
2ASMBTSG |  | COULD BE ANYTHING | -75.5 | 0
2APMBTSG |  | COULD BE ANYTHING | -146 | -146 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | A2&COUNTIF($A$2:A2,A2)
2APMBTSG |  | COULD BE ANYTHING | -149 | -149 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Create a unique Number by appending the Occurrence Count No. to the Fitting No.
Q11141 |  | COULD BE ANYTHING | -20 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | This formula expands the range to be examined as you drag the formula down.
2ASMBTSG |  | COULD BE ANYTHING |  | 75.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | In this way the Countif is only returning a number within a limited range
2APMBTSG |  | COULD BE ANYTHING | -20 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | As the range grows so will (potentially) the number of Occurences (INSTANCES)
Q11139 |  | COULD BE ANYTHING | -20 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COUNTIF(FITTING_No.[NAME])
3STMBTSG |  | COULD BE ANYTHING | -25 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Counts the number of Occurences as a check
Q11144 |  | COULD BE ANYTHING | 0 | 0
Q11141 |  | COULD BE ANYTHING | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IFERROR(VLOOKUP($A2&G$1,DataBase_25K,3,FALSE),0)
Q11141 |  | COULD BE ANYTHING | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pulls the first Occurrence etc… of "WEIGHT OFF"
Q11139 |  | COULD BE ANYTHING | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IFERROR(VLOOKUP($A2&M$1,DataBase_25K,4,FALSE),0)
Q11139 |  | COULD BE ANYTHING | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pulls the first Occurrence etc… of "WEIGHT ON"
PART-12XE-001 |  | COULD BE ANYTHING | 0 | 0
PART-12XE-001 |  | COULD BE ANYTHING | 0 | 0
TYUIOP123 |  | COULD BE ANYTHING | -134 | 0
PART-12XE-001 |  | COULD BE ANYTHING | 0 | 0
Q11144 |  | COULD BE ANYTHING | -5 | 0
Q11144 |  | COULD BE ANYTHING | 0 | 5
Q11144 |  | COULD BE ANYTHING | -9 | 0
Q11146 |  | COULD BE ANYTHING | -1 | 0
Q11146 |  | COULD BE ANYTHING | 0 | 1
1589-/4589-01258 |  | COULD BE ANYTHING | -13 | 0
ERR-2346 |  | COULD BE ANYTHING | -132000 | 0
 |  | COULD BE ANYTHING | 0 | 0
ERR-2346 |  | COULD BE ANYTHING | 0 | -132000
3659/-158-0015 |  | COULD BE ANYTHING | 0 | 0
TYUIOP123 |  | COULD BE ANYTHING | 0 | 134
1589-/4589-01258 |  | COULD BE ANYTHING | 0 | 0
TEST-9999 |  | COULD BE ANYTHING | -0.1 | 0
TYUIOP123 |  | COULD BE ANYTHING | 0 | 0
QWERTY67-90 |  | COULD BE ANYTHING | -96 | 0
TYUIOP123 |  | COULD BE ANYTHING | 0 | 0
TYUI-890-158 |  | COULD BE ANYTHING | 0 | 0
TYUI-890-159 |  | COULD BE ANYTHING | 0 | 0
TYUI-890-160 |  | COULD BE ANYTHING | -6 | 0