M
Macarius Brownfield
I have a set of data in a sheet:
'Matrix'.
Column F is the part name (contains multiples of each type--named the range matrix_pn).
Column A is a space vehicle number (Values = 1-12; Rows 2 - ~1000; range named matrix_sv).
Column H is the serial number (numbers or text; rows 2 - ~1000; named range matrix_sn).
There is a grid on another sheet:
'Summary Matrix'.
The grid is Part number vertically in Column E (static data, matching names in column F of the Matrix sheet); horizontally, Columns G through R in Row 2 are the Space Vehicle Numbers 1-12.
Sample Data:
-- Matrix Sheet --
|| Col A (SV #) || Col F (P/N) || Col H (S/N) ||
1...................Tank.............2001xx
2...................Tank.............2002xx
3...................Tank.............2003xx
12..................Tank.............2012xx
12..................Wing.............001
1...................Wing.............002
2...................Wing.............100102
3...................Wing.............100103
-- Summary Matrix Sheet --
|| Col E (P/N) || Col G (SV1) || (SV2) || ... || Col R (SV12) ||
Tank.............2001xx.........2002xx..........2012xx
Wing.............002............100102..........001
The solution for the grid is an array formula (that I cannot piece successfully on my own) that uses the part name (Col E) in the 'Summary Matrix' sheet to find unique serial numbers in the 'Matrix' sheet using 2 conditions: the Space Vehicle and the Part Name. Another complication is that the part name list in the Summary Matrix may have duplicates as well--when for example 2 parts of that type are on the vehicle.
I started to use this formula in Cell G4--the first cell of the grid:
{=INDEX(matrix_sn,MATCH(1,(matrix_pn=$E11)*(matrix_sv=1),0))}
This produces the serial numbers correctly unless there is a duplicate part number, where it repeats the first serial number found.
Please your expedient assistance would be extremely appreciated.
V/r,
M
'Matrix'.
Column F is the part name (contains multiples of each type--named the range matrix_pn).
Column A is a space vehicle number (Values = 1-12; Rows 2 - ~1000; range named matrix_sv).
Column H is the serial number (numbers or text; rows 2 - ~1000; named range matrix_sn).
There is a grid on another sheet:
'Summary Matrix'.
The grid is Part number vertically in Column E (static data, matching names in column F of the Matrix sheet); horizontally, Columns G through R in Row 2 are the Space Vehicle Numbers 1-12.
Sample Data:
-- Matrix Sheet --
|| Col A (SV #) || Col F (P/N) || Col H (S/N) ||
1...................Tank.............2001xx
2...................Tank.............2002xx
3...................Tank.............2003xx
12..................Tank.............2012xx
12..................Wing.............001
1...................Wing.............002
2...................Wing.............100102
3...................Wing.............100103
-- Summary Matrix Sheet --
|| Col E (P/N) || Col G (SV1) || (SV2) || ... || Col R (SV12) ||
Tank.............2001xx.........2002xx..........2012xx
Wing.............002............100102..........001
The solution for the grid is an array formula (that I cannot piece successfully on my own) that uses the part name (Col E) in the 'Summary Matrix' sheet to find unique serial numbers in the 'Matrix' sheet using 2 conditions: the Space Vehicle and the Part Name. Another complication is that the part name list in the Summary Matrix may have duplicates as well--when for example 2 parts of that type are on the vehicle.
I started to use this formula in Cell G4--the first cell of the grid:
{=INDEX(matrix_sn,MATCH(1,(matrix_pn=$E11)*(matrix_sv=1),0))}
This produces the serial numbers correctly unless there is a duplicate part number, where it repeats the first serial number found.
Please your expedient assistance would be extremely appreciated.
V/r,
M