+ Reply to Thread
Results 1 to 19 of 19

Incrementing a row by more than one when dragging down

  1. #1
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

    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.
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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.
    Last edited by AliGW; 04-17-2021 at 06:16 AM. Reason: Typo
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    If it ain't broke!
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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. #5
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Hi AliGW. Yes this could be an option.
    Last edited by AliGW; 04-17-2021 at 06:04 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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. #7
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

    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. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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.

    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.
    Last edited by AliGW; 04-17-2021 at 06:31 AM.

  9. #9
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

    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.
    Last edited by AliGW; 04-17-2021 at 06:45 AM. Reason: PLEASE don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

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

    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.

  11. #11
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Will do AliGW

  12. #12
    Registered User
    Join Date
    08-08-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel Office 365, PowerBI, Excel 2010, Excel 2013
    Posts
    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. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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. #14
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

    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?
    Last edited by GoldenOldie; 04-17-2021 at 08:55 AM. Reason: Unnecessary clutter. Sorry.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

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

    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)
    Last edited by AliGW; 04-17-2021 at 08:55 AM.

  16. #16
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Yes with a formula AliGW

  17. #17
    Registered User
    Join Date
    04-15-2010
    Location
    Wolverhampton
    MS-Off Ver
    Excel 2007
    Posts
    20

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

    Top marks again AliGW.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

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

    Pleasure!

  19. #19
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    264

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. dragging sheets like dragging cells.
    By cyclop2509 in forum Excel General
    Replies: 2
    Last Post: 03-02-2017, 05:33 AM
  2. [SOLVED] Incrementing Colums while dragging Index formula across row
    By dwhite30518 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-19-2015, 12:42 PM
  3. [SOLVED] Incrementing ROW when dragging across columns
    By jshaw82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 04:16 PM
  4. Dragging Across Columns, Incrementing Rows
    By Fpob in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-07-2012, 10:56 AM
  5. [SOLVED] Dragging formulas across columns while incrementing rows
    By gautamacharya in forum Excel General
    Replies: 4
    Last Post: 10-12-2011, 04:42 PM
  6. Incrementing
    By Odisey in forum Excel General
    Replies: 4
    Last Post: 10-10-2008, 11:42 AM
  7. Dragging & incrementing cell values
    By Terry Bennett in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2006, 07:20 PM

Bookmarks

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