Hi all,

I am tring to copy the below code down in my exel spread sheet so it does the following

=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),1)

=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),2)

=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),3)

=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),4)

=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),5)

------------------------------------------------------------------------------

=LARGE(IF(Result!$N$2:$N$1000=8,Result!$O$2:$O$1000,""),1)

=LARGE(IF(Result!$N$2:$N$1000=8,Result!$O$2:$O$1000,""),2)

=LARGE(IF(Result!$N$2:$N$1000=8,Result!$O$2:$O$1000,""),3)

=LARGE(IF(Result!$N$2:$N$1000=8,Result!$O$2:$O$1000,""),4)

=LARGE(IF(Result!$N$2:$N$1000=8,Result!$O$2:$O$1000,""),5)

------------------------------------------------------------------------------

(--- indicates a empty row)

I have tried to drap and drop it down so the number after Result!$N$2:$N$1000= increases each time but it just loops round!!!! any ideas how i can just copy it down???

I am tring to copy the below code down in my exel spread sheet so it does the following

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

--------------------------

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

=LARGE(IF(Result!$N$2:$N$1

--------------------------

(--- indicates a empty row)

I have tried to drap and drop it down so the number after Result!$N$2:$N$1000= increases each time but it just loops round!!!! any ideas how i can just copy it down???

```
=MOD(ROW(),6)
```

will do the job. It will produce the numbers 1 to 5 in rows 1:5, a zero in row 6 (which is blank in your worksheet) and resume with 1 thru 5 in row 7 etc.If your first formula isn't in row 1 you should reduce the result returned by ROW() by a number equal to the number of rows above the first formula. For example,

```
=MOD(ROW()-2,6)
```

would be the formula if the first of your formulas is in row 3.Here is your formula with the above idea incorporated:-

```
=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),MOD(ROW()-2,6))
```

Result!$N$2:$N$1000=7.

This formula will count from 0 upward each 6 rows.

```
=INT(ROW()/6)
```

Again, you can adjust the result to start counting at a row other than 1 in the manner already described. Similarly, if you need to start counting with 7 this formula would do the job, =7+INT(ROW()/6). Note that this method would return the wrong result for each row number divisible by 6. That can be compensated by enlarging the formula a little, but in your case that is not necessary because precisely those rows are blank.Here is your formula with the above thought incorporated:-

```
=LARGE(IF(Result!$N$2:$N$1000=INT((ROW()-2)/6),Result!$O$2:$O$1000,""),MOD(ROW()-2,6))
```

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Round-tripping through notepad extracts the resultant text only on the way out and interprets as a formula on the way in.

See attachment for use and below code block for main concept...

Open in new window

ComplexFormulaPaste.xlsx