Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
I have formula's that return text strings in A1:A7 (note these also
may return <blank. So there maybe text in A3 and A6 only or a combination of any of the 7. How could I return in A1, the 1st string returned from A1:A7, the 2nd string in A2 etc etc In my example of A3:A6 above, the value essentially given in A3 would appear in A1, the value appearing in A6 would appear in A2 and thus nothing in A3:A7? I hope I have not described this in a very confusing manner Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
One easy option to float it up in an adjacent col C
In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to C7. Results in col C (Hide col B) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Sean" wrote: I have formula's that return text strings in A1:A7 (note these also may return <blank. So there maybe text in A3 and A6 only or a combination of any of the 7. How could I return in A1, the 1st string returned from A1:A7, the 2nd string in A2 etc etc In my example of A3:A6 above, the value essentially given in A3 would appear in A1, the value appearing in A6 would appear in A2 and thus nothing in A3:A7? I hope I have not described this in a very confusing manner Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
A UDF maybe
Alt + f11 to open VB editor. Right clicl 'This Workboo' and insert module and paste the code below in. You call it like this =popdown($A$1:$A$7,ROW(A1)) If dragged down it will return the populated cells in order and start returning 'Error' when they run out. Alternatively call it like this =popdown($A$1:$A$7,4) To return the 4th instance Function PopDown(rng As Range, instance As Integer) For Each c In rng If c.Value < "" Then instfound = instfound + 1 If instfound = instance Then PopDown = c.Value End If Next If PopDown = 0 Then PopDown = "Error" End Function Mike "Sean" wrote: I have formula's that return text strings in A1:A7 (note these also may return <blank. So there maybe text in A3 and A6 only or a combination of any of the 7. How could I return in A1, the 1st string returned from A1:A7, the 2nd string in A2 etc etc In my example of A3:A6 above, the value essentially given in A3 would appear in A1, the value appearing in A6 would appear in A2 and thus nothing in A3:A7? I hope I have not described this in a very confusing manner Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
Thanks Max / Mike
On your formula Max, I've tried a simple test and it works, but when I apply it to my actual data I get a <blank in a cell which I expect it to return a string. I'll explain- In D186 I have a formula =IF(AB$178="","","Sunday in - "&AB$178), this produces a text string (I have <blank returns in D180:D185) Based on your Formula for "B" I have entered in H186 =IF(D186="","",ROW()). this produces 186 as a result (I have <blank returns in H180:H185) Based on your formula for "C" I have entered in I180 =IF(ROW()COUNT(H $180:H$186),"",INDEX(D$180:D$186,SMALL(H$180:H$186 ,ROW()))), this produces a <blank in I180. I would have expected it to return the text string that is displayed in D186. has it anything to do with how I return my value in D186? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
ROW() is sensitive to the cell its placed in. If your data starts in other
than row1, you could replace ROW() with ROWS($1:1), viz: In H180: =IF(D180="","",ROWS($1:1)) In I180: =IF(ROWS($1:1)COUNT(H$180:H$186),"",INDEX(D$180:D $186,SMALL(H$180:H$186,ROWS($1:1)))) Copy H180:I180 down to I186, and it'll work fine, returns in I180:I186 -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Sean" wrote in message ... Thanks Max / Mike On your formula Max, I've tried a simple test and it works, but when I apply it to my actual data I get a <blank in a cell which I expect it to return a string. I'll explain- In D186 I have a formula =IF(AB$178="","","Sunday in - "&AB$178), this produces a text string (I have <blank returns in D180:D185) Based on your Formula for "B" I have entered in H186 =IF(D186="","",ROW()). this produces 186 as a result (I have <blank returns in H180:H185) Based on your formula for "C" I have entered in I180 =IF(ROW()COUNT(H $180:H$186),"",INDEX(D$180:D$186,SMALL(H$180:H$186 ,ROW()))), this produces a <blank in I180. I would have expected it to return the text string that is displayed in D186. has it anything to do with how I return my value in D186? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
Max thanks for that, its absolutely ingenius
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a String in a 7 Row Range Q
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Sean" wrote Max thanks for that, its absolutely ingenius |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return first TWO or THREE words in string | Excel Discussion (Misc queries) | |||
return 1st word in string | Excel Discussion (Misc queries) | |||
return an empty string in VBA | Excel Discussion (Misc queries) | |||
return partial string | Excel Worksheet Functions | |||
return cell address of longest text string in a range | Excel Discussion (Misc queries) |