Choose from a row with limts

[jade_00]

Hello all,

When I first attempted the following it seemed remarkably simple, yet I am still unable to find a solution.......

I want to have a column that selects the leftmost value in its corresponding row without repeating a previous result.

Ie. Column A is the answer column, and Columns B:E have a list of values in rows.

So, A1 will pick B1, and A2 will pick the leftmost value in Row 2 that has not been selected in A1. A3 will pick the leftmost value in Row 3 that has not been selected by A1 or A2 etc etc.

Make sense? Seems simple, but I haven't been able to figure it out.

I need a formula, not a macro, beacuse I need to enter this at unpredictable times.

Any help would be appreciated.

 

[Andrew Poulsom]

In A2 type:

=INDEX(B2:E2,,MATCH(TRUE,COUNTIF(A$1:A1,B2:E2)=0,FALSE))

and press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.

Copy the formula down. It will return #N/A if all of the numbers in B:E for the current row have already been used.

 

[jade_00]

Absolutely fantastic - this is EXACTLY what I needed.

Brilliant, thank you so much.