Parsing thru columns to find the last number
in [Programming]
|
From: Bailey on 5 May 2010 12:06 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
From: Mike H on 5 May 2010 12:20 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
From: Bailey on 5 May 2010 18:34 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
|
Pages: 1 Prev: Referencing Defined Name in another workbook Next: I want to format lines in my footers |