Populating an Indicator (X) When Two Criteria Are Met

[laclair003]

Please see below table. I'm trying to assign an "X" in column NIS2 when any record sharing the same TRANS ID (column A) has an "X" in column NOT IN STOCK. My obstacle is some times the NOT IN STOCK column "X" may be the first, middle or last record for a given TRANS ID. My attempts using IF AND & OR combination formulas have had very limited success. Any insight is appreciated! TRANS ID SEQUENCE PART PART2 NOT IN STOCK NIS2 52268329 1 23237402 Floor Liners 52268329 2 84073612 Floor Liners X 52270748 1 22958431 Splash Guards 52270748 2 23278169 Splash Guards 52270755 1 22958431 Splash Guards 52270755 2 23278169 Splash Guards 52271238 1 22894857 Splash Guards X 52271238 2 23387353 Splash Guards 52271268 3 19567412 Sill Plate5 52274322 1 23435023 Exhaust Systems 52276033 1 23381376 Floor Liners 52276033 2 84370635 Floor Liners X

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[Gerald Higgins]

Hi, welcome to the board.

Should the results look like this . . .

TRANS ID SEQUENCE PART PART2 NOT IN STOCK NIS2
52268329 1 23237402 Floor Liners X
52268329 2 84073612 Floor Liners X X
52270748 1 22958431 Splash Guards
52270748 2 23278169 Splash Guards
52270755 1 22958431 Splash Guards
52270755 2 23278169 Splash Guards
52271238 1 22894857 Splash Guards X X
52271238 2 23387353 Splash Guards X
52271268 3 19567412 Sill Plate5
52274322 1 23435023 Exhaust Systems
52276033 1 23381376 Floor Liners X
52276033 2 84370635 Floor Liners X X

If YES, then assuming TRANSID is in column A and so on, try this in column F . . .

=COUNTIFS(A$2:A$13,A2,E$2:E$13,"X")

Remember, older versions of Excel don't have the COUNTIFS function.

 

[laclair003]

Thanks for the quick response. I think your suggestion will work just fine. Thanks so much!