Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Oct 21, 2021

Formula

I have been tasked with creating a formula that looks into a big ole list of 317 items on a sheet with multiple columns and 317 rows.  These 317 items have to be checked on over the course of 12 months at a rate of 26 per month.  So basically I have to create an inspection list which consists of placing these items into a separate workbook into individual cells under column names of Jan, Feb, Mar, Apr, and so on.  All these items are on a single sheet but I have to RANDOMLY SELECT these items and plot them into a different workbook under 12 month column names.

 

Can anyone help me with this?

  • Carl_61 

    Sheesh.

    Once more.

     

    Let's say the units are in Electric!E3:E321.

    In Electric!CP3, enter the formula =RAND()

    Fill down to Electric!CP321 for example by double-clicking the fill handle of cell Electric!CP3.

     

    On another sheet, for example Sheet1:

    Enter Jan in Sheet1!D1, and fill to the right to Sheet1!O1; you should automatically get Feb, Mar, ..., Dec.

    In Sheet1!D2, enter the formula

     

    =INDEX(Electric!$E$3:$E$321,RANK(INDEX(Electric!$CP$3:$CP$321,26*(COLUMN(D2)-COLUMN($D$2))+ROW(D2)-ROW($D$2)+1),Electric!$CP$3:$CP$321))

     

    Fill down to row 27, then to the right to column O (or vice versa).

    The random sample will be updated each time Excel recalculates. If you want to "fix" a sample, select the range, copy it and paste it elsewhere as values.

  • Carl_61 

    Let's say the units are in A2:A318.

    In B2, enter the formula =RAND()

    Fill down to B318, for example by double-clicking the fill handle of cell B2.

     

    Enter Jan in D1, and fill to the right to O1; you should automatically get Feb, Mar, ..., Dec.

    In D2, enter the formula

     

    =INDEX($A$2:$A$318,RANK(INDEX($B$2:$B$318,26*(COLUMN(D2)-COLUMN($D$2))+ROW(D2)-ROW($D$2)+1),$B$2:$B$318))

     

    Fill down to row 27, then to the right to column O (or vice versa).

    The random sample will be updated each time Excel recalculates. If you want to "fix" a sample, select the range, copy it and paste it elsewhere as values.

    • Carl_61's avatar
      Carl_61
      Iron Contributor
      I failed to mention that these items are electric in nature are on a sheet named "Electric". So this formula needs to pull the information onto "Sheet1" until I come up with a different name. So where do I insert Sheet1?
      • Carl_61 

        Try this:

         

        Let's say the units are in Electric!A2:A318.

        In Electric!B2, enter the formula =RAND()

        Fill down to Electric!B318, for example by double-clicking the fill handle of cell Electric!B2.

         

        On another sheet, for example Sheet1:

        Enter Jan in Sheet1!D1, and fill to the right to Sheet1!O1; you should automatically get Feb, Mar, ..., Dec.

        In Sheet1!D2, enter the formula

         

        =INDEX(Electric!$A$2:$A$318,RANK(INDEX(Electric!$B$2:$B$318,26*(COLUMN(D2)-COLUMN($D$2))+ROW(D2)-ROW($D$2)+1),Electric!$B$2:$B$318))

         

        Fill down to row 27, then to the right to column O (or vice versa).

        The random sample will be updated each time Excel recalculates. If you want to "fix" a sample, select the range, copy it and paste it elsewhere as values.

Resources