Find the first & subsequent cells to contain text in a column & re

  • Thread starter Thread starter M
  • Start date Start date
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,
 
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.
 
I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko.
 
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(Table,,MATCH(H$1,A$1:D$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")
 
Thank you so much!

T. Valko said:
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(Table,,MATCH(H$1,A$1:D$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")
Click to expand...
 
Back
Top