Drag formula, but in a pattern that skips rows.

I assume that by "words, you do not literally mean the 5 letters in words? I assume that A6-8, A14-16 contain numbers? Correct? If not pease post a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Try this array formula in C1, copied down:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER($A$1:$A$22),ROW($A$1:$A$22)),ROWS(C$1:C1))),"")



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I know a macro that can do this, not sure of a formula

Please Login or Register  to view this content.

What is to be copied from where, to where?????

No. see it now.

So. Is the number of unwanted rows between the wanted rows REALLY variable? It was 9 rows beofre the first block and 10 rows before the second.
Do the KIAs always begin with the word "subject".

PLEASE use representative data. Otherwise we may waste a lot of time working on patterns that don't exist.

Non Array formula, should be faster. Paste in A1 and fill down

Formula:

Please Login or Register  to view this content.

In A2, copied across and down. Much easier now!!

=OFFSET(Sheet1!B$11,10*INT((ROWS($1:1)-1)/10)+ROWS($1:1),,,)

Excel is all about patterns. Once you know what the pattern is... the rest falls into place, like falling off a log. Easy(ish)

=OFFSET(Sheet1!B$11,10*INT((ROWS($1:1)-1)/10)+ROWS($1:1),,,)

red: start with this cell
Cyan: staring with zero: increment by 1 every 10 rows
orange: and then multiply that number by 10
Once you have a toal, move down that number of rows,

looking at this again, there might just be an error in it. try it on a larger dataset and let me know...
Green: and then add the row number

If EVERYTHING that is to be returned is text, modify as shown:

=OFFSET(Sheet1!B$11,10*INT((ROWS($1:1)-1)/10)+ROWS($1:1),,,)&""

Ha. You smashed the pattern. Of course there are (sometimes) Plans B that can be brought into action. An array formula in sheet2, cell A2, copied across and down:


=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(ISERROR(MATCH(Sheet1!$B$12:$B$41,Sheet1!$B$2:$B$11,0)),IF(Sheet1!$B$12:$B$41<>"",ROW(Sheet1!$B$12:$C$41))),ROWS($1:1))),"")


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.