I have six columns of data and am unsure how to get to the final replacement
of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes
that have replaced the col a item. Not all items have been replaced five
times, some none, some only two, in this case col b would be blank and or col
C would be blank. My goal is to find the last item in the columns that were
replaced and put it in a column on it's own. So the outcome will be colA is
replaced by Col G.

The data looks like this.

A B C D E F G
z k o p i So this was replaced four times and the end result is "i"
x z this one only replaced by z once
y k g this was replaced twice with end result of g

All the end results should be in column G.

I tried to sort but some of the blank cells don't past the isblank() test
unless I put the cursor in them and then and hit enter - I can't do that for
this many items. Is there an easier way?


-- l
___________________
Dedicated to learning from the experts

Hi,

Try this in Col G to return the rightmost value

=LOOKUP(2,1/(A1:F1<""),A1:F1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bailey" wrote:

I have six columns of data and am unsure how to get to the final replacement
of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes
that have replaced the col a item. Not all items have been replaced five
times, some none, some only two, in this case col b would be blank and or col
C would be blank. My goal is to find the last item in the columns that were
replaced and put it in a column on it's own. So the outcome will be colA is
replaced by Col G.

The data looks like this.

A B C D E F G
z k o p i So this was replaced four times and the end result is "i"
x z this one only replaced by z once
y k g this was replaced twice with end result of g

All the end results should be in column G.

I tried to sort but some of the blank cells don't past the isblank() test
unless I put the cursor in them and then and hit enter - I can't do that for
this many items. Is there an easier way?


-- l
___________________
Dedicated to learning from the experts

Excellent - thank you. I always forget about the horizontal lookup., thank
you!!!
--
___________________
Dedicated to learning from the experts


"Mike H" wrote:

Hi,

Try this in Col G to return the rightmost value

=LOOKUP(2,1/(A1:F1<""),A1:F1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bailey" wrote:

I have six columns of data and am unsure how to get to the final replacement
of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes
that have replaced the col a item. Not all items have been replaced five
times, some none, some only two, in this case col b would be blank and or col
C would be blank. My goal is to find the last item in the columns that were
replaced and put it in a column on it's own. So the outcome will be colA is
replaced by Col G.

The data looks like this.

A B C D E F G
z k o p i So this was replaced four times and the end result is "i"
x z this one only replaced by z once
y k g this was replaced twice with end result of g

All the end results should be in column G.

I tried to sort but some of the blank cells don't past the isblank() test
unless I put the cursor in them and then and hit enter - I can't do that for
this many items. Is there an easier way?


-- l
___________________
Dedicated to learning from the experts