Searching a column for text (any text) and if it's there place it in a specific cell

Hi guys, I've made a trading based spreadsheet that changes every day. Basically when a stock shows certain characteristics it will appear in a certain cell in a column (otherwise the cell will be ""). It will usually be the only peice of text in that column, but maybe one other if they are exactly equal values (It's % change from previous close, so a tie is possible sometimes). Usually should be the only text in that column though. I'm wondering if it's possible to search thru that 99% empty column (or "" to be precise) and put the piece of text if it exists in a desired cell at the top of the spreadsheet. It's so I don't have to keep scrolling around to find the output of my spreadsheet. Hopefully I can organise it a bit (lot) better.

Craig.

P.S. I've found this forum very helpful for most of my dramas. Really good stuff.

Hi Gherkin,

welcome to the forum.
Can you share a sample workbook along with your expected output. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

Once you are in reply window, click on "go advanced" button (located on bottom right side) and you'll see a paper clip icon, click that and follow the wizard. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

In cell D4, you mentioned "any text in V column", so does that mean any text from v column would be shown here ?


Can you explain using an example ? thanks.


Regards,
DILIPandey
<click on below * if this helps>

Okay.. np.
Now, can you upload a revised workbook along with your expected output. thanks.


Regards,
DILIPandey
<click on below * if this helps>

okay.. I guess you need any text from columns O,P,Q,R,S,T as per your remarks in left side of sheet.

Now, I believe you do not need any text from O5:T5 as these appears as headings.. correct ?

Regards,
DILIPandey
<click on below * if this helps>

Okay.. see the attached file :-

Example for forum2.xls



Regards,
DILIPandey
<click on below * if this helps>

Okay..

Did you found any #NAME in my above attachment ?
Randbetween function is just giving random numbers between 1 and 22 which is used by offset function to pick values in the same column. Suggest you to look into Offset function to understand the logic. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

I did not see any #NAME error in my workbook.

since you need "any text" from the column, hence I used RandBetween function and since the data on the left side of these columns are 22 rows long, I used randbetween(1,22) which will generate any number between 1 and 22 e.g., suppose it generates 5, offset function will get value of 5th cell from the heading for that column and similarly for all columns... I see this quite simple.

Open my workbook and keep pressing F9 key and you'll see that the formulas will keep on picking random values from respective columns.

Offset function and randbetween function both works in Excel2003 hence I am not sure why you are getting #Name error.


Regards,
DILIPandey
<click on below * if this helps>

IN D4
=LOOKUP(2, 1/($O$6:$O$27<>""), $O$6:$O$27)

So you can understand and duplicate it.
Lookup is looking for the first instance of 2, in the array 1/(O6:O27<>"")
O6:O27 is going to return False or 0 when the cell is blank and True or 1 when it's True
So in your example, we get this array
{1/0, 1/0, 1/0, 1/1, 1/0....} 1/0 results in an error which lookup ignores.
The lookup will never find a 2 so it will return the last value (ignoring errors) that it finds which will be the last cell that has text in it.
Does that help?

FYI, You are probably getting #NAME error because in Excel 2003, you need to enable the "Analysis Toolpak Addin" to use RANDBETWEEN. I am not sure that Dili's solution will work for you though.

Thanks ChemistB

Gherkin, please mark this thread as [SOLVED].. thx


Regards,
DILIPandey
<click on below * if this helps>