Incrementing a row by more than one when dragging down

1. Incrementing a row by more than one when dragging down

Hi I'm hoping that someone can help me with a solution that I've been trying to resolve for days. I'm designing a greyhound results spreadsheet to show the winning traps in each race. The results are in sets of 6 dogs per race and I'm only looking to record results where there are winners (no dead heats). In need to know how do I increment row I3 to N3 and subsequent rows when dragging down. I need it check the next set of race results and so forth. IAs I mentioned I'm only going to base it on races that involve 6 dogs so I need to be able to drag the formula down so that it will always move to the next set of 6 dogs in the proceeding race. Basically I need cell I3 to be similar to =VLOOKUP(\$I\$1,D8:E13,\$E\$1,0) when I drag down and so forth for cells J3, K3, L3, M3 & N3 and proceeding rows below this.

Many thanks in anticipation.

2. Re: Incrementing a row by more than one when dragging down

I cannot believe you're still using Excel 97 - are you???

There are instructions at the top of the page explaining how to attach your sample workbook.

3. Re: Incrementing a row by more than one when dragging down

If it ain't broke!

4. Re: Incrementing a row by more than one when dragging down

Because you have used satanic merged cells, the easiest solution will involve a helper column (could be hidden) to overcome the poor judgement on your part.

Is this an option? If not, I won't help unless the merged cells can go.

5. Re: Incrementing a row by more than one when dragging down

Hi AliGW. Yes this could be an option.

6. Re: Incrementing a row by more than one when dragging down

Add column G thus (can be hidden once set up):

Excel 2016 (Windows) 32 bit
G
1
2
Race 1
3
Race 1
4
Race 1
5
Race 1
6
Race 1
7
Race 1
8
Race 2
9
Race 2
10
Race 2
11
Race 2
12
Race 2
13
Race 2
14
Race 3
15
Race 3
16
Race 3
17
Race 3
18
Race 3
19
Race 3
 Sheet: Sheet1

Then in J2 copied across and down:

=LOOKUP(2,1/((\$G\$2:\$G\$19=\$I2)*(\$D\$2:\$D\$19=J\$1)),\$E\$2:\$E\$19)

Excel 2016 (Windows) 32 bit
I
J
K
L
M
N
O
1
Trap 1
Trap 2
Trap 3
Trap 4
Trap 5
Trap 6
2
Race 1
0
0
1
0
0
0
3
Race 2
1
0
0
0
0
0
4
Race 3
0
0
0
1
0
0
 Sheet: Sheet1

7. Re: Incrementing a row by more than one when dragging down

Hi AliGW.

This works for all the existing data however when I add the next race set of results each row cell returns #N/A

8. Re: Incrementing a row by more than one when dragging down

Yeah, well you'll have to adapt the lookup ranges to cover the full table, won't you, otherwise it won't find any matches:

=LOOKUP(2,1/((\$G\$2:\$G\$100=\$I2)*(\$D\$2:\$D\$100=J\$1)),\$E\$2:\$E\$100)

Just make them as long as they will ever need to be.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

9. Re: Incrementing a row by more than one when dragging down

I promise you that this was purely a school boy error on my part.
I really appreciate your assistance, time and effort in helping me AliGW.

10. Re: Incrementing a row by more than one when dragging down

Of course it was!

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

11. Re: Incrementing a row by more than one when dragging down

Will do AliGW

12. Re: Incrementing a row by more than one when dragging down

An alternative method would be to use Indirect to calculate the range with offsets to accommodate the header row, e.g.

=VLOOKUP(\$I\$1,INDIRECT("\$D"&SUM((ROW(I2)-1)*6-4)&":"&"\$E"&SUM((ROW(I2)-1)*6)+1),\$E\$1,0)

13. Re: Incrementing a row by more than one when dragging down

OFFSET and INDIRECT are both volatile, so OK on small datasets, but not recommended on large ones.

However, I wouldn't recommend anything that facilitates merged cells: they need avoiding like the plague.

14. Re: Incrementing a row by more than one when dragging down

Hi again AliGW.

What's the easiest way to enter the race numbers 6 times rather than manually?

15. Re: Incrementing a row by more than one when dragging down

You mean using a formula?

Try this copied down:

="Race "&COUNTIF(B\$2:B2,1)

16. Re: Incrementing a row by more than one when dragging down

Yes with a formula AliGW

17. Re: Incrementing a row by more than one when dragging down

Top marks again AliGW.

Pleasure!

19. Re: Incrementing a row by more than one when dragging down

Another option without helper. May operate slow on large datasets.
PHP Code:
``` =MAX(IF((LOOKUP(ROW(\$F\$2:\$F\$100),ROW(\$F\$2:\$F\$100)/(\$F\$2:\$F\$100<>""),\$F\$2:\$F\$100=\$H2)*(\$D\$2:\$D\$100=I\$1)),\$E\$2:\$E\$100))  ```
Press CTRL+SHIFT+ENTER instead of just pressing ENTER.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1