Find the first & subsequent cells to contain text in & repeat

M

M

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

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
(in column A) contains 4 or more characters. So the formula should return 4
then 6 then 8 then 8 then 11 then 11 etc.

I have the following two formulas that work but I can’t successfully combine
them

To return row numbers that contain text (Thanks to Biff)

=IF(ROWS(C$3:C3)<=COUNTIF(A$1:A$12,"*"),SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$3:C3)),"")

Logic test to either carry out the above or repeat the row number in the
cell above

=OR($C1=$C2,LEN(INDIRECT("A"&$C2))<3)

I hope this is clear

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top