Spreadsheet: 271243d1381870930-challenge-for-real-excel-experts-complex-categorization-on-multiple-criteria-categorization-wrong-answers-with-explanation
Sheet: Sheet2

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | What I have….. |  |  |  |  | What I need…..
 |  | ID | Type | Color |  |  |  |  |  |  | CP | date |  |  | Feedback Category |  | Feedback |  |  |  |  |  |  | ID & Type | ID & Type & Color |  |  | Feedback Category- AAAA001 |  |  |  |  | Feedback Category- AAAA001
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-01-01 00:00:00 |  |  | AAAA001 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | ID & Type |  |  |  |  | ID & Type
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-01-01 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1300 |  |  |  |  |  |  |  |  |  |  | Month/Year | CP1 | CP2 | CP3 |  | Month/Year | CP1 | CP2 | CP3
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-01-01 00:00:00 |  |  | AAAA003 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Jan 2013 |  |  |  |  | Jan 2013 | 0 | 0 | 0
 |  | R100 | car | red |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Feb 2013 |  |  |  |  | Feb 2013 | 0 | 0 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Mar 2013 |  |  |  |  | Mar 2013 | 0 | 0 | 0
 |  | R100 | car | red |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA002 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | Apr 2013 |  |  |  |  | Apr 2013 | 1 | 1 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA002 |  | [NAME] 200 |  |  |  |  |  |  |  |  |  |  | May 2013 |  |  |  |  | May 2013 | 1 | 0 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA002 |  | [NAME] 201 |  |  |  |  |  |  |  |  |  |  | Jun 2013 |  |  |  |  | Jun 2013 | 0 | 0 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 202 |  |  |  |  |  |  |  |  |  |  | Jul 2013 |  |  |  |  | Jul 2013 | 0 | 0 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 203 |  |  |  |  |  |  |  |  |  |  | Aug 2013 |  |  |  |  | Aug 2013 | 0 | 0 | 0
 |  | R100 | car | red |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA003 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | Sep 2013 |  |  |  |  | Sep 2013 | 0 | 0 | 0
 |  | R100 | car | pink |  |  |  |  |  |  | CP1 | 2013-01-29 00:00:00 |  |  | AAAA003 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Oct 2013 |  |  |  |  | Oct 2013 | 0 | 0 | 0
 |  | R544 | truck | blue |  |  |  |  |  |  | CP1 | 2013-02-09 00:00:00 |  |  | AAAA001 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Nov 2013 |  |  |  |  | Nov 2013 | 0 | 0 | 0
 |  | R544 | truck | blue |  |  |  |  |  |  | CP1 | 2013-02-09 00:00:00 |  |  | AAAA002 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Dec 2013 |  |  |  |  | Dec 2013 | 0 | 0 | 0
 |  | R544 | truck | blue |  |  |  |  |  |  | CP1 | 2013-02-09 00:00:00 |  |  | AAAA003 |  | [NAME]
 |  | R544 | truck | blue |  |  |  |  |  |  | CP1 | 2013-02-09 00:00:00 |  |  | AAAA003 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | Feedback Category- AAAA001 |  |  |  |  | Feedback Category- AAAA001
 |  | R544 | truck | blue |  |  |  |  |  |  | CP1 | 2013-02-09 00:00:00 |  |  | AAAA003 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | ID & Type & Color |  |  |  |  | ID & Type & Color
 |  | R323 | truck | black |  |  |  |  |  |  | CP1 | 2013-02-14 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Month/Year | CP1 | CP2 | CP3 |  | Month/Year | CP1 | CP2 | CP3
 |  | R323 | truck | black |  |  |  |  |  |  | CP1 | 2013-02-14 00:00:00 |  |  | AAAA002 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Jan 2013 |  |  |  |  | Jan 2013 | 0 | 0 | 0
 |  | R323 | truck | black |  |  |  |  |  |  | CP1 | 2013-02-14 00:00:00 |  |  | AAAA003 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Feb 2013 |  |  |  |  | Feb 2013 | 0 | 0 | 0
 |  | R675 | car | yellow |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA001 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Mar 2013 |  |  |  |  | Mar 2013 | 0 | 0 | 0
 |  | R675 | car | black |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA001 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Apr 2013 |  |  |  |  | Apr 2013 | 3 | 2 | 0
 |  | R675 | car | yellow |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1400 |  |  |  |  |  |  |  |  |  |  | May 2013 |  |  |  |  | May 2013 | 2 | 0 | 0
 |  | R675 | car | black |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1500 |  |  |  |  |  |  |  |  |  |  | Jun 2013 |  |  |  |  | Jun 2013 | 0 | 0 | 0
 |  | R675 | car | yellow |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA003 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Jul 2013 |  |  |  |  | Jul 2013 | 0 | 0 | 0
 |  | R675 | car | black |  |  |  |  |  |  | CP1 | 2013-02-25 00:00:00 |  |  | AAAA003 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Aug 2013 |  |  |  |  | Aug 2013 | 0 | 0 | 0
 |  | R200 | van | blue |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 1003 |  |  |  |  |  |  |  |  |  |  | Sep 2013 |  |  |  |  | Sep 2013 | 0 | 0 | 0
 |  | R200 | van | blue |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 105 |  |  |  |  |  |  |  |  |  |  | Oct 2013 |  |  |  |  | Oct 2013 | 0 | 0 | 0
 |  | R200 | van | blue |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 1300 |  |  |  |  |  |  |  |  |  |  | Nov 2013 |  |  |  |  | Nov 2013 | 0 | 0 | 0
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Dec 2013 |  |  |  |  | Dec 2013 | 0 | 0 | 0
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 1004
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 18888
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Looking at data going from broad to specific
 |  | R200 | van | blue |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA002 |  | No input required at this time. |  |  |  |  |  |  |  |  |  | For Feedback Category- AAAA001 for ID & Type for CP1 in Apr 2013
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1300 |  |  |  |  |  |  |  |  |  |  | Month/Year: data gathered in April
 |  | R200 | van | blue |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1301 |  |  |  |  |  |  |  |  |  |  | CP: data for April (only CP1 and CP2)
 |  | R200 | van | green |  |  |  |  |  |  | CP1 | 2013-04-12 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1302 |  |  |  |  |  |  |  |  |  |  | Feedback Category: data for CP1 (AAAA001 has 7, AAAA002 has 2, AAAA003 has 2)
 |  | R100 | car | red |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA001 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | Look specifically at AAAA001 for ID & Type (bold red text): even though there is varying feedback (7 total) there is only one ID & Type (R200van)... So that only counts as 1.
 |  | R100 | car | red |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA001 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | that gives you the number in the black cell with yellow text
 |  | R100 | car | red |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA001 |  | [NAME] 302
 |  | R100 | car | pink |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | Looking at data going from broad to specific
 |  | R100 | car | red |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA002 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  | For Feedback Category- AAAA001 for ID & Type & Color for CP1 in Apr 2013
 |  | R100 | car | pink |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA002 |  | No feedback is needed. |  |  |  |  |  |  |  |  |  |  | Month/Year: data gathered in April
 |  | R100 | car | red |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA003 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | CP: data for April (only CP1 and CP2)
 |  | R100 | car | pink |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA003 |  | [NAME] 600 |  |  |  |  |  |  |  |  |  |  | Feedback Category: data for CP1 (AAAA001 has 7, AAAA002 has 2, AAAA003 has 2)
 |  | R100 | car | pink |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA003 |  | [NAME] 601 |  |  |  |  |  |  |  |  |  |  | Look specifically at AAAA001 for ID & Type & Color, there are 2 kinds (R200vanblue and R200vangreen).
 |  | R100 | car | pink |  |  |  |  |  |  | CP2 | 2013-04-10 00:00:00 |  |  | AAAA003 |  | [NAME] |  |  |  |  |  |  |  |  |  |  | For R200vanblue there are 3 different feedbacks (1003, 105 and 1300). That gives the blue cell value
 |  | R544 | truck | blue |  |  |  |  |  |  | CP2 | 2013-05-09 00:00:00 |  |  | AAAA001 |  | No input required at this time. |  |  |  |  |  |  |  |  |  |  | For R200vangreen there are 2 different feedbacks (1004 and 18888)…. NOTE "No input required at this time." and "No feedback is needed." are NOT COUNTED. That gives the green cell value.
 |  | R544 | truck | blue |  |  |  |  |  |  | CP2 | 2013-05-09 00:00:00 |  |  | AAAA002 |  | No input required at this time.
 |  | R544 | truck | blue |  |  |  |  |  |  | CP2 | 2013-05-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1001
 |  | R544 | truck | blue |  |  |  |  |  |  | CP2 | 2013-05-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1001
 |  | R544 | truck | blue |  |  |  |  |  |  | CP2 | 2013-05-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1100
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA001 |  | [NAME] 700
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA001 |  | [NAME] 701
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA001 |  | [NAME] 702
 |  | R899 | van | black |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA001 |  | [NAME] feedback 703
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA002 |  | [NAME]
 |  | R899 | van | black |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA002 |  | [NAME]
 |  | R899 | van | black |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA002 |  | [NAME]
 |  | R899 | van | red |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA003 |  | [NAME]
 |  | R899 | van | black |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1300
 |  | R899 | van | black |  |  |  |  |  |  | CP1 | 2013-05-25 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1300
 |  | R544 | truck | blue |  |  |  |  |  |  | CP3 | 2013-06-09 00:00:00 |  |  | AAAA001 |  | No input required at this time.
 |  | R544 | truck | blue |  |  |  |  |  |  | CP3 | 2013-06-09 00:00:00 |  |  | AAAA002 |  | No input required at this time.
 |  | R544 | truck | blue |  |  |  |  |  |  | CP3 | 2013-06-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1002
 |  | R544 | truck | blue |  |  |  |  |  |  | CP3 | 2013-06-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1002
 |  | R544 | truck | blue |  |  |  |  |  |  | CP3 | 2013-06-09 00:00:00 |  |  | AAAA003 |  | [NAME] feedback 1200
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA001 |  | No input required at this time.
 |  | R200 | van | green |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA001 |  | No input required at this time.
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1300
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1301
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1302
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA002 |  | [NAME] feedback 1303
 |  | R200 | van | green |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA002 |  | No input required at this time.
 |  | R200 | van | blue |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA003 |  | No input required at this time.
 |  | R200 | van | green |  |  |  |  |  |  | CP2 | 2013-07-02 00:00:00 |  |  | AAAA003 |  | No input required at this time.