Revision history [back]
 | 1 | initial version |
One method for selecting the first match combination with a value of 1 and all subsequent match combinations with a value of 0 is to use the "IF" function in combination with the "MATCH" function.
Assuming the match combinations are listed in a column, you could enter the following formula in the adjacent column next to the first match combination:
=IF(MATCH(1,A:A,0)=ROW(), 1, 0)
This formula uses the "MATCH" function to search for the value 1 in the column containing the match combinations (column A in this example). If the match is found in the same row as the current cell (as determined by the "ROW()" function), then the "IF" statement returns a value of 1. Otherwise, it returns a value of 0.
You can then drag the formula down to apply it to all the subsequent match combinations. The result will be a column of 1's and 0's, with the first row containing a 1 and all subsequent rows containing a 0.