|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 26th, 2009, 08:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Find the first & subsequent cells to contain text in a column & re
I have a column that contains empty cells and text. I want to return the row
reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help. 
|
|
#2
February 26th, 2009, 09:23 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column & re
Hi,
Put his formula in any cell (except row 1). Drag down and it will return the row numbers of columnA that have text. It will start producing #NUM errors when it stops finding text in the range =SMALL(IF(ISTEXT($A$1:$A$12),ROW($A$1:$A$12)),ROW( )-ROW($D$1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#3
February 26th, 2009, 09:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column & re
Assume you want the results starting in cell C1.
Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#4
February 26th, 2009, 09:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Jst realised my formula is over complicated,
Try this array entered =SMALL(IF(ISTEXT($A$1:$A$12),ROW($A$1:$A$12)),ROW( )) Mike "Mike H" wrote: Hi, Put his formula in any cell (except row 1). Drag down and it will return the row numbers of columnA that have text. It will start producing #NUM errors when it stops finding text in the range =SMALL(IF(ISTEXT($A$1:$A$12),ROW($A$1:$A$12)),ROW( )-ROW($D$1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#5
February 26th, 2009, 10:04 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Biff,
Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#6
February 26th, 2009, 10:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),
That's an error trap. It's more efficient than: =IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))), COUNTIF(A$1:A$12,"*") Returns the total number of *text* entries in the range and compares that number to number of rows the formula is copied to: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), If the number of rows the formula is copied to is greater than the total number of text entries in the range then the formula returns a blank (""). Otherwise, SMALL(...,n) would return a #NUM! error. -- Biff Microsoft Excel MVP "Mike H" wrote: Biff, Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#7
February 26th, 2009, 10:43 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Ah i see, I was happy to produce NUM errors with my formula when the matches
ran out but see this is tidier. Thank you. Mike "T. Valko" wrote: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), That's an error trap. It's more efficient than: =IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))), COUNTIF(A$1:A$12,"*") Returns the total number of *text* entries in the range and compares that number to number of rows the formula is copied to: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), If the number of rows the formula is copied to is greater than the total number of text entries in the range then the formula returns a blank (""). Otherwise, SMALL(...,n) would return a #NUM! error. -- Biff Microsoft Excel MVP "Mike H" wrote: Biff, Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#8
February 26th, 2009, 10:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
You're welcome!
-- Biff Microsoft Excel MVP "Mike H" wrote: Ah i see, I was happy to produce NUM errors with my formula when the matches ran out but see this is tidier. Thank you. Mike "T. Valko" wrote: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), That's an error trap. It's more efficient than: =IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))), COUNTIF(A$1:A$12,"*") Returns the total number of *text* entries in the range and compares that number to number of rows the formula is copied to: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), If the number of rows the formula is copied to is greater than the total number of text entries in the range then the formula returns a blank (""). Otherwise, SMALL(...,n) would return a #NUM! error. -- Biff Microsoft Excel MVP "Mike H" wrote: Biff, Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#9
February 27th, 2009, 09:36 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Thanks for your help however is there a solution to include repeating the row
number if there is more than 4 characters?? I have just tried Biff's reply. I enter the formula as an array in 1 cell and then copy it down. This correctly lists the row numbers but does not repeat (see the last bit of my first post) Thanks again. "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "Mike H" wrote: Ah i see, I was happy to produce NUM errors with my formula when the matches ran out but see this is tidier. Thank you. Mike "T. Valko" wrote: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), That's an error trap. It's more efficient than: =IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))), COUNTIF(A$1:A$12,"*") Returns the total number of *text* entries in the range and compares that number to number of rows the formula is copied to: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), If the number of rows the formula is copied to is greater than the total number of text entries in the range then the formula returns a blank (""). Otherwise, SMALL(...,n) would return a #NUM! error. -- Biff Microsoft Excel MVP "Mike H" wrote: Biff, Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
|
#10
February 27th, 2009, 07:45 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Hi M!
Use Biff's formula in C1 (or mine, if you prefer), and then in C2: =IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE)))) Again, this is an array formula. Not a very elegant formula but it works. Note that there are several callouts to $a$1:$a$12 which you will need to adjust as necessary. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Thanks for your help however is there a solution to include repeating the row number if there is more than 4 characters?? I have just tried Biff's reply. I enter the formula as an array in 1 cell and then copy it down. This correctly lists the row numbers but does not repeat (see the last bit of my first post) Thanks again. "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "Mike H" wrote: Ah i see, I was happy to produce NUM errors with my formula when the matches ran out but see this is tidier. Thank you. Mike "T. Valko" wrote: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), That's an error trap. It's more efficient than: =IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))), COUNTIF(A$1:A$12,"*") Returns the total number of *text* entries in the range and compares that number to number of rows the formula is copied to: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), If the number of rows the formula is copied to is greater than the total number of text entries in the range then the formula returns a blank (""). Otherwise, SMALL(...,n) would return a #NUM! error. -- Biff Microsoft Excel MVP "Mike H" wrote: Biff, Just curious but what are you testing for in the first part of your formula, =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"), why not just =SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1)) what am I missing? Mike "T. Valko" wrote: Assume you want the results starting in cell C1. Try this array formula** entered in C1 and copied down until you get blanks: =IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"") This assumes your *REAL* data range does in fact start on row 1. If it doesn't start on row 1 then we'll need to make a slight adjustment. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. As for your "complication", I don't think that can be done with a single formula. If it can I'd love to see it! -- Biff Microsoft Excel MVP "M" wrote: I have a column that contains empty cells and text. I want to return the row reference of the cells that contain text. For example… A2 = empty cell A3 = empty cell A4 = 2L A5 = empty cell A6 = 2W A7 = empty cell A8 = 2L2W A9 = empty cell A10 = empty cell A11 = 2L2W A12 = empty cell The formula would return 4. Thanks to Luke I have… {=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))} However I need to copy this formula down a column. The formula should return 4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I can’t sort the data as I have other equations in the sheet. To add to this problem I need to repeat the row number if the original cell contains 4 or more characters. So the formula should return 4 then 6 then 8 then 8 then 11 then 11 etc. Thanks for your help.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
