| Home |
| Search |
| Today's Posts |
|
#1
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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
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 cant 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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column & re
On Thu, 26 Feb 2009 12:07:02 -0800, 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. Here's one way. This formula must be **array-entered** in some row. Then fill down until you start getting blanks. "rng" refers to the range of interest. If you are using a version of Excel prior to Excel 2007, rng cannot be an entire column (but could be $A$1:$A$65535) =IF(ISERR(ADDRESS(LARGE(ISTEXT(rng)*ROW(rng), COUNTA(rng)+1-ROWS($1:1)),1,4)),"",ADDRESS(LARGE( ISTEXT(rng)*ROW(rng),COUNTA(rng)+1-ROWS($1:1)),1,4)) To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Then Fill Down until you start getting blanks. --ron |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
That doesn't repeat the 11.
-- Biff Microsoft Excel MVP "Luke M" wrote: 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 cant 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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column & re
On Thu, 26 Feb 2009 12:07:02 -0800, 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. I already posted a formula solution to your first problem. For your complication, it is easiest done using a VBA solution. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ===================================== Option Explicit Sub TextAdr() Dim c As Range Dim rSrc As Range Dim rDest As Range Dim i As Long Dim lNumEntries As Long Dim lNumResults As Long Set rSrc = Range("RNG") lNumEntries = Application.WorksheetFunction.CountA(rSrc) Set rDest = Range("C1") lNumResults = lNumEntries For Each c In rSrc If Len(c.Value) = 4 Then lNumResults = lNumResults + 1 End If Next c 'clear destination cells +1 Range(rDest, rDest.Offset(lNumResults, 0)).ClearContents 'get addresses i = 0 For Each c In rSrc Select Case Len(c.Value) Case Is = 4 rDest.Offset(i, 0).Value = c.Address rDest.Offset(i + 1, 0).Value = c.Address i = i + 2 Case Is 1 rDest.Offset(i, 0).Value = c.Address i = i + 1 End Select Next c End Sub =================================== --ron |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Luke, this is very close to what I want but there are a couple of errors.
Unfortunately after a lot of head scratching I cant alter it myself. 1) When I replace all the references to the correct ones the formula returns a €œ#REF!€ error. I assume it is something to do with the offset but cant work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 cant 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. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
On Fri, 27 Feb 2009 01:36:02 -0800, 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. Did you see my previously posted VBA solution to this problem of yours? --ron |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Hi Ron,
Yes I have noticed your post (thanks for your help) however I have never used VBA so dont feel I know what I am doing and secondly other people in the office have to use this spreadsheet so I want to make it as simple as possible €“ copying a formula down a column €“ it cant get any simpler! thanks again for your help anyway. "Ron Rosenfeld" wrote: On Fri, 27 Feb 2009 01:36:02 -0800, 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. Did you see my previously posted VBA solution to this problem of yours? --ron |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
On Mon, 2 Mar 2009 05:18:01 -0800, M wrote:
Hi Ron, Yes I have noticed your post (thanks for your help) however I have never used VBA so don’t feel I know what I am doing and secondly other people in the office have to use this spreadsheet so I want to make it as simple as possible – copying a formula down a column – it can’t get any simpler! thanks again for your help anyway. If you can't come up with a formula to do what you want, with the multiple entries for size =4, take another look at what I posted and the instructions. If implemented as intended, it will automatically update your first sheet without any formula copying, button pushing, etc. The "trigger" to do the update would be merely making an entry on sheet 2. --ron |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I cant alter it myself. 1) When I replace all the references to the correct ones the formula returns a €œ#REF!€ error. I assume it is something to do with the offset but cant work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 cant 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. |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Hi Luke, I really amazed today when I look at here your solution wondering
about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I cant alter it myself. 1) When I replace all the references to the correct ones the formula returns a €œ#REF!€ error. I assume it is something to do with the offset but cant work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 cant 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. |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Try this...
You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
|
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
T. Valko,
Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
|
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Im going to take a break for a few hours. When I return I'll put together a
sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
|
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Here's a small sample file that demonstrates this.
xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
|
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 |
|
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Narnimar" wrote in message ... I reaized my mistake while copying webpage in the table. Excel is working great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 |
|
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E? "T. Valko" wrote: Good deal. Thanks for the feedback! -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... I reaized my mistake while copying webpage in the table. Excel is working great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 |
|
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Ok, I assume you mean the items in column A that correspond to the non-blank
rows in Valx. Redefine the named range table to include column A: Name: Table Refers to: =$A$2:$D$7 Change the formula in I1 to: =COUNTA(INDEX(Table,,MATCH(H1,A1:D1,0))) Change this expression in the formula in F1 (in 2 places): From: MATCH(H$1,B$1:D$1,0) To: MATCH(H$1,A$1:D$1,0) Make sure you re-enter as an array! And finally, enter this array formula in E1 and copy down: =IF(ROWS(E$1:E1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1:D$1,0))<"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"") -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Dear T. Valko, By the way, how to get the nonblank list of items from column A in E? "T. Valko" wrote: Good deal. Thanks for the feedback! -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... I reaized my mistake while copying webpage in the table. Excel is working great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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 |
|
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
Thank you so much!
"T. Valko" wrote: Ok, I assume you mean the items in column A that correspond to the non-blank rows in Valx. Redefine the named range table to include column A: Name: Table Refers to: =$A$2:$D$7 Change the formula in I1 to: =COUNTA(INDEX(Table,,MATCH(H1,A1:D1,0))) Change this expression in the formula in F1 (in 2 places): From: MATCH(H$1,B$1:D$1,0) To: MATCH(H$1,A$1:D$1,0) Make sure you re-enter as an array! And finally, enter this array formula in E1 and copy down: =IF(ROWS(E$1:E1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1:D$1,0))<"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"") -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Dear T. Valko, By the way, how to get the nonblank list of items from column A in E? "T. Valko" wrote: Good deal. Thanks for the feedback! -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... I reaized my mistake while copying webpage in the table. Excel is working great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
|
#29
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find the first & subsequent cells to contain text in a column
You're welcome!
-- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Thank you so much! "T. Valko" wrote: Ok, I assume you mean the items in column A that correspond to the non-blank rows in Valx. Redefine the named range table to include column A: Name: Table Refers to: =$A$2:$D$7 Change the formula in I1 to: =COUNTA(INDEX(Table,,MATCH(H1,A1:D1,0))) Change this expression in the formula in F1 (in 2 places): From: MATCH(H$1,B$1:D$1,0) To: MATCH(H$1,A$1:D$1,0) Make sure you re-enter as an array! And finally, enter this array formula in E1 and copy down: =IF(ROWS(E$1:E1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1:D$1,0))<"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"") -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Dear T. Valko, By the way, how to get the nonblank list of items from column A in E? "T. Valko" wrote: Good deal. Thanks for the feedback! -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... I reaized my mistake while copying webpage in the table. Excel is working great now. Thank you so much T. Valko. "T. Valko" wrote: Here's a small sample file that demonstrates this. xNarnimar.xls 14kb http://cjoint.com/?hAdCaKigG6 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Im going to take a break for a few hours. When I return I'll put together a sample file that does what you want. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... T. Valko, Thanks. Sorry for the wrong Subject above. I did exactly as per the your instruction but if I copy the cells down appear blank except F1. Is there something to do more? Cn you please see it again? With regards narnimar, "T. Valko" wrote: Try this... You're subject line says find TEXT but your data is numeric assuming "blank" means empty/blank cells. B1:D1 = column headers Valx B2:D7 data you want to extract H1 = Valx Create this defined range name... Goto the menu InsertNameDefine Name: Table Refers to: =$B$2:$D$7 Enter this formula in I1. It will return the count of records. =COUNTA(INDEX(Table,,MATCH(H1,B1:D1,0))) Enter this array formula** in F1: =IF(ROWS(F$1:F1)<=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1:D$1,0))<"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1:D$1,0)),"") ** 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. Copy down to a number of rows that is at least equal to the maximum count of entries in any of your Val columns. For example, the Val column with the maximum number of entries is Val3 with 5 entries. So, you'd have to copy the formula down to at least 5 rows. -- Biff Microsoft Excel MVP "Narnimar" wrote in message ... Hi Luke, I really amazed today when I look at here your solution wondering about my unresolved issue which is closer to it. I dont know if it is right on my part to put the question in between this discusion. The data base is like this example:- A B C D E F H Item Val1 Val2 Val3 Bus 2 Val2 Car 2 blank 5 Cycle 35 Bus 1 2 1 Horse 1 Train blank blank 3 Flight blank blank 1 Cycle 20 35 Blank Horse Blank 1 2 if I type Val2 in H1 the formula in column F should return a nonblank list and its corresponding row value from column from column A as shown above. The formula in F should be able to guess and return nonblank values from columns B or C or D based on the value in H1 which would be header name of these columns! This is possible? Any question please revert back. Please help me. "Luke M" wrote: Hmm. One more attempt he =IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)<2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE))))) Again, an array formula. I tried to setup my sheet as you described, data is in rows 37-57. Ron's VBA solution is probably more elegant, but at least now if you want a formula answer, you have one. I must admit, this was an interesting challenge. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "M" wrote: Luke, this is very close to what I want but there are a couple of errors. Unfortunately after a lot of head scratching I can't alter it myself. 1) When I replace all the references to the correct ones the formula returns a "#REF!" error. I assume it is something to do with the offset but can't work it out. Ref A1:A15 should be F37:F57. This formula is entered into column M. My formula to find the first entry is in row 87 and this formula starts in row 88 onwards 2) If there are more then 4 characters in the last entry it does not repeat (The repeat works fine as long as the last entry is less than 4 characters. I am not sure if this can be solved or if I need to add a dummy entry that is less than 4 characters?? Thanks again for your help. If there is a solution to the first point I can start using the formula. Great! "T. Valko" wrote: That doesn't repeat the 11. -- Biff Microsoft Excel MVP "Luke M" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I use Text to wipe out subsequent dates? | Excel Discussion (Misc queries) | |||
| Formula that will change subsequent cells in a column to match fir | Excel Discussion (Misc queries) | |||
| Elimnating subsequent cells | Excel Discussion (Misc queries) | |||
| how do I keep column titles on subsequent pages of a pivot table | Excel Worksheet Functions | |||
| Tool fro entering data in subsequent cells | Excel Discussion (Misc queries) | |||
Linear Mode
