Hi, I found the formula from this thread (http://www.teachexcel.com/excel-help...to.php?i=51213) but not sure how I can make this works if my data goes from left to right, does anyone has an idea..?
Hi, I found the formula from this thread (http://www.teachexcel.com/excel-help...to.php?i=51213) but not sure how I can make this works if my data goes from left to right, does anyone has an idea..?
Sorry forgot to put an example; so i wanted to return the value from the table below..
Return value ColumnB Column C Column D Column E
Test 1 test1
Test 2 test2
Test 3 test3
Test 4 test4
See the @pb71 (Forum Contributer) post #5 on http://www.excelforum.com/l/735632-return-non-blank-cells-from-a-range.html
I add a new solution for Excel 2010 and prior with this regular formula
Formula:Please Login or Register to view this content.
See the file for clarification
Sorry for the last post
Use the following formula for left to right
Formula:Please Login or Register to view this content.
See the file for clarification
Try this...
Assuming the data is text as your sample demonstrates and that there are no formulas in the range that return blanks "".
Data Range
A B C D E 1 test1 test1 2 test2 test2 3 test3 test3 4 test4 test4
This formula entered in A1 and copied down:
=INDEX(B1:E1,MATCH("*",B1:E1,0))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Enter A1 and copy down
Formula:Please Login or Register to view this content.
v A B C D E F G 1 One One Two 2 Two Two Three 3 Three Three 4 Five Five Four
Last edited by AlKey; 06-08-2016 at 08:37 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks