dgoscinski
New Member
The problem:
I have to track my suppliers, their part numbers, and defects over time. Main data found in columns A (Date), C (Supplier), D (Part Number), and F (AIM Code).
If a supplier has a defect for a part, I need to count the incident. This part is simple. (Column L)
However, I also need to count REPEAT (Column K) incidents in a time range. How many times did the same defect occur on the same part?
In the above scenario, Supplier Boogie has 8 total incidents on 5 different Part Numbers.
7L14 5A638 AD needs to return 1 for CLAP repeat. (2 incidents for Clap-1) It has repeated once. (If there were 3 incidents for clap, there would be 2 repeats. SO first incident doesn't count as a repeat.)
JL14 5A638 FC, JL14 5A638 BB, and JL14 5A638 CC are NOT repeats despite the CRACK. They have 1 incident each, but since the crack is on a different part it is not a repeat.
JL14 5A638 EB and 7L14 5A638 AD also have the same defect. They are one incident, but not repeats. and the last 7L14 is FLASH not CLAP, so it is not a repeat of the above parts.
Lastly, I will need to add a Date portion to the formula. Ultimately, I need to know how many times any of the above conditions have happened in the past 12 months. If an incident happened in Dec 2018, then I don't want it counted.
Any suggestions? Can this be done with COUNTIFS like I did to populate the table next to the data? Or would VBA be a better method. I tried using pivot tables while experimenting. I can get results that I need. But the end results actually are assigned a grade. (This part I know how to do and do not include in the data set.) The letter grade based on incidents and repeats has to be exported into a report card. Pivot tables being TOO dynamic screw up my reference formulas as soon as a new part is introduced to the dataset.
I have to track my suppliers, their part numbers, and defects over time. Main data found in columns A (Date), C (Supplier), D (Part Number), and F (AIM Code).
If a supplier has a defect for a part, I need to count the incident. This part is simple. (Column L)
However, I also need to count REPEAT (Column K) incidents in a time range. How many times did the same defect occur on the same part?
In the above scenario, Supplier Boogie has 8 total incidents on 5 different Part Numbers.
7L14 5A638 AD needs to return 1 for CLAP repeat. (2 incidents for Clap-1) It has repeated once. (If there were 3 incidents for clap, there would be 2 repeats. SO first incident doesn't count as a repeat.)
JL14 5A638 FC, JL14 5A638 BB, and JL14 5A638 CC are NOT repeats despite the CRACK. They have 1 incident each, but since the crack is on a different part it is not a repeat.
JL14 5A638 EB and 7L14 5A638 AD also have the same defect. They are one incident, but not repeats. and the last 7L14 is FLASH not CLAP, so it is not a repeat of the above parts.
Lastly, I will need to add a Date portion to the formula. Ultimately, I need to know how many times any of the above conditions have happened in the past 12 months. If an incident happened in Dec 2018, then I don't want it counted.
Any suggestions? Can this be done with COUNTIFS like I did to populate the table next to the data? Or would VBA be a better method. I tried using pivot tables while experimenting. I can get results that I need. But the end results actually are assigned a grade. (This part I know how to do and do not include in the data set.) The letter grade based on incidents and repeats has to be exported into a report card. Pivot tables being TOO dynamic screw up my reference formulas as soon as a new part is introduced to the dataset.