Hello-
I'm looking for a formula that will search an entire row and return any text it finds. Each row only has 1 word in it but the word can appear in any one of 20 columns. All other cells in the row are blank. I'm trying to create 1 vertical list of all the words that are spread throughout a large table.
Visual aid is attached.
Thanks in advance
in a column beyond CI and copy down as far as the data extends.Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Try this, copied down...
=INDEX($A1:$M1,MATCH(TRUE,INDEX(($A1:$M1<>0),0),0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this in cell CI1
=LOOKUP(2,1/(A1:CH1<>""),A1:CH1)
and copy down column CI.
bdika
Here's another one...
Data Range
This formula entered in A1 and copied down:
=INDEX(B1:E1,MATCH("zzzzz",B1:E1))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony, what is the significance of the z's? You used 5, Trvor used 50
Last edited by FDibbins; 07-23-2015 at 07:06 PM. Reason: cant spell
@Ford: just an arbitrary number of alpha characters that will be alphabetically greater than anything in the data area, and hence will not be found ... returning the "next lower" alpha string.
Regards, Trevor
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks