Incrementing a row by more than one when dragging down

[GoldenOldie]

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.

[AliGW]

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.

[GoldenOldie]

If it ain't broke!

[AliGW]

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.

[GoldenOldie]

Hi AliGW. Yes this could be an option.

[AliGW]

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

[GoldenOldie]

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

[AliGW]

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.

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

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.

[GoldenOldie]

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.

[AliGW]

Of course it was!

Glad to help.

Administrative Note:

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!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[GoldenOldie]

Will do AliGW

[DMTD]

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)

[AliGW]

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.

[GoldenOldie]

Hi again AliGW.

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

[AliGW]

You mean using a formula?

PLEASE edit your post - what did I say about quoting unnecessarily? It's just CLUTTER!

Try this copied down:

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

[GoldenOldie]

Yes with a formula AliGW

[GoldenOldie]

Top marks again AliGW.

[AliGW]

Pleasure!

[T.I.]

Another option without helper. May operate slow on large datasets.

=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.