+ Reply to Thread
Results 1 to 5 of 5

Return every nth value in column

  1. #1
    Registered User
    Join Date
    08-15-2013
    Location
    Norfolk, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Return every nth value in column

    Hi all,

    Basically I have a HUGE dataset, which i am trying to minimise by extracting every nth row, (this can vary - but for now assume every 5th row).

    I have attached an example of the spreadsheet...

    The columns I have are:
    A - x value
    B - y value
    C - height

    I want to duplicate rows A to C, for every 5th row, into (for example) columns G-H. The values in each row are linked (i.e. h corresponds to the coordinate (x,y)) - so these can't become mixed

    Cells A3:C3 to G3:I3
    Cells A8:C8 to G4:I4
    Cells A13:C13 to G5:I5
    ...and so on.

    I have tried to use the OFFSET function, =OFFSET($A$3,ROW()*5,0), but it doesn't return every 5th row, and i'm now too sure what it is doing!

    I have >1,000,000 rows of data in case this affects the formulae

    I hope somebody can help!

    Thanks & Kind Regards,

    Rachel

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Return every nth value in column

    Pls attach a sample excel file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return every nth value in column

    Entered in G3:

    =OFFSET(A$3,ROWS(G$3:G3)*5-5,0)

    Copy across to I3 then down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    08-15-2013
    Location
    Norfolk, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Return every nth value in column

    Amazing thank you so much, it works perfectly!

    So basically for every nth row, i just replace the 5 in the formula (it works for 10 rows too)

    Thanks!

    Rachel

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return every nth value in column

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Compare multiple columns (column A,column B,Column C) and return value (Column D)
    By john008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2015, 08:24 AM
  2. [SOLVED] How to return address of the column or cell I select, not just return the value?
    By qzqzjcjp in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-18-2014, 04:57 PM
  3. Replies: 8
    Last Post: 05-16-2013, 05:28 PM
  4. Replies: 4
    Last Post: 03-13-2013, 12:38 PM
  5. [SOLVED] Search Value of column E if exist return value of column B put result in column P
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-23-2013, 11:39 AM
  6. Replies: 3
    Last Post: 01-10-2006, 06:10 AM
  7. Index Row and return data in a Single Column:No return
    By Domenic in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM

Tags for this Thread

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