I need some help/suggestions with an analysis I am performing based on product serial numbers.
Background:
I have a spreadsheet of 500 or so serial numbers for one of our electronic products. The serial numbers cover 4-5 different model numbers. Each of these models contains 30-50 pieces of a certain component.
Units produced over a certain time period contained components from a supplier that has been determined to be provided sub par quality. Their component batches (defined by date) are traceable in our production process down to the circuit board position in each unit. So I have another matrix that lists all of the serial numbers with any components from this supplier and which board positions and batch they contain.
The complicated thing is that the second matrix repeats each serial number for every board position and batch.
Basically what I want to do is query the first list of serial numbers against the second matrix and have it spit out all matches.
So if serial number A is in the second matrix 20 times, I want to identify all 20 lines, if serial number B is listed 35 times, I want to identify all 35, an so on.
I am usually somewhat resourceful in figuring out a formula or method to analyze, but have been struggling with this one.
Appreciate any and all thoughts or suggestions on this and let me know if anything above needs clarification.
Background:
I have a spreadsheet of 500 or so serial numbers for one of our electronic products. The serial numbers cover 4-5 different model numbers. Each of these models contains 30-50 pieces of a certain component.
Units produced over a certain time period contained components from a supplier that has been determined to be provided sub par quality. Their component batches (defined by date) are traceable in our production process down to the circuit board position in each unit. So I have another matrix that lists all of the serial numbers with any components from this supplier and which board positions and batch they contain.
The complicated thing is that the second matrix repeats each serial number for every board position and batch.
Basically what I want to do is query the first list of serial numbers against the second matrix and have it spit out all matches.
So if serial number A is in the second matrix 20 times, I want to identify all 20 lines, if serial number B is listed 35 times, I want to identify all 35, an so on.
I am usually somewhat resourceful in figuring out a formula or method to analyze, but have been struggling with this one.
Appreciate any and all thoughts or suggestions on this and let me know if anything above needs clarification.
