=IF(A1<>"","=LARGE(IF(Result!$N$2:$N$1000="&A1&",Result!$O$2:$O$1000,""""),"&B1&")","")
ComplexFormulaPaste.xlsx
=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.=MOD(ROW()-2,6)
would be the formula if the first of your formulas is in row 3.=LARGE(IF(Result!$N$2:$N$1000=7,Result!$O$2:$O$1000,""),MOD(ROW()-2,6))
=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.=LARGE(IF(Result!$N$2:$N$1000=INT((ROW()-2)/6),Result!$O$2:$O$1000,""),MOD(ROW()-2,6))
Title | # Comments | Views | Activity |
---|---|---|---|
How to Auto-fill data in Database; VBA Code Mixed Combining Two User Forms | 4 | 29 | |
Excel 2016 Hiding Toolbars | 7 | 23 | |
Top 10 with duplicates in Excel Table | 4 | 13 | |
MIN, using ARRAY | 4 | 17 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!