Forum Discussion

sholland985's avatar
sholland985
Copper Contributor
Aug 09, 2024

selecting every 46th row in excel then copy/ paste into new page

Hi,

I have approx. 160,000 rows of data

Ideally I want to choose every 46th row; and then use them as my sample.

OR

How can I choose 3466 random rows from an excel document of 160,000 and put in new document

Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Steel Contributor
    Aug 09, 2024

    you can use
    =chooserows(A1:Z160000, sequence(int(160000/46),,46,46))

    for random

    =chooserows(A1:Z160000, randarray(3466,,1,160000,TRUE))

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 09, 2024

    sholland985 

    =FILTER(A1:F160000,MOD(ROW(A1:F160000),46)=0)

     

    If you have access to the FILTER function you can use this formula.

     

    In legacy Excel e.g. Excel 2013 you can use Advanced Filter or Power Query.

Resources