LinkBack URL
About LinkBacks
Between A1:A9, text appears in one cell and the rest are blank.
I want A10 to be populated with the 1 piece of text between A1:A9.
When it was a number I could use:
=max(a1:a9)
How do I do it with text?
Between A1:A9, text appears in one cell and the rest are blank.
I want A10 to be populated with the 1 piece of text between A1:A9.
When it was a number I could use:
=max(a1:a9)
How do I do it with text?
=LOOKUP(REPT("z",255),A1:A9)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Will that diplay Z if the field is populated? The whole issue is, I have 9 columns in which a stock ticker appears only if it is the highest value. In the 10th column I want to check the first 9 and tell me the one piece of text that appeared in all of them.
I've tried using if/or functions. I can't figure this out. I'm actually giving up and doing it all manually, but it seems like it should be simple.
Thanks for trying though.
nope it will display whatever text which is in column a1:a9 provided only on cell is filled
did you try it?
rept("z",255) is 255 z's since lookup cant find a string with 255 z's if returns the last value it can find
Last edited by martindwilson; 11-03-2013 at 07:59 PM.
It's not working... I don't understand why. It populates the correct value in the first cell, but as soon as the data is populated in another cell in the range it just displays nothing. I will upload the file I'm working with in a second.
Last edited by joshz2012; 11-03-2013 at 08:15 PM.
Here is the file showing it's not working. Look at row AE5 down to see it not working. It only populates the first cell with the value. From there, if the column with the data isn't the same as the first, it reads it as blank.
Help appreciated.
its something to do with whats in the cells they are not truly blank something is being imported into them
try =ISBLANK(B11) it returns false even though the cell appears blank
Fair enough. I copied and "pasted values" so I'm not sure how to get rid of that. I'm close to just entering the data manually. But thanks for the suggestion.
how did you paste? did you paste special text?
No, i mean, all of these were populated by formulas. So I copied it into this workbook and pasted specials "Paste as values" so it would read as text and blank instead of all having equations.
ah its copied ""
try
=IFERROR(INDEX(B3:J3,MATCH(TRUE,INDEX(B3:J3<>"",0),0)),"")
or
=IFERROR(LOOKUP(2,1/(B3:J3<>""),B3:J3),"")
Last edited by martindwilson; 11-03-2013 at 09:15 PM.
That works perfectly, thank you!
And that's right, I had them display "" so it would read as blank. But I have no idea how you figured that out, as I can't see the quotation marks in the formula bar.
Thanks so much for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks