SOLVED

Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2871649%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20been%20tasked%20with%20creating%20a%20formula%20that%20looks%20into%20a%20big%20ole%20list%20of%20317%20items%20on%20a%20sheet%20with%20multiple%20columns%20and%20317%20rows.%26nbsp%3B%20These%20317%20items%20have%20to%20be%20checked%20on%20over%20the%20course%20of%2012%20months%20at%20a%20rate%20of%2026%20per%20month.%26nbsp%3B%20So%20basically%20I%20have%20to%20create%20an%20inspection%20list%20which%20consists%20of%20placing%20these%20items%20into%20a%20separate%20workbook%20into%20individual%20cells%20under%20column%20names%20of%20Jan%2C%20Feb%2C%20Mar%2C%20Apr%2C%20and%20so%20on.%26nbsp%3B%20All%20these%20items%20are%20on%20a%20single%20sheet%20but%20I%20have%20to%20RANDOMLY%20SELECT%20these%20items%20and%20plot%20them%20into%20a%20different%20workbook%20under%2012%20month%20column%20names.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20anyone%20help%20me%20with%20this%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2871649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871667%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171720%22%20target%3D%22_blank%22%3E%40Carl_61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20units%20are%20in%20A2%3AA318.%3C%2FP%3E%0A%3CP%3EIn%20B2%2C%20enter%20the%20formula%20%3DRAND()%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20B318%2C%20for%20example%20by%20double-clicking%20the%20fill%20handle%20of%20cell%20B2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20Jan%20in%20D1%2C%20and%20fill%20to%20the%20right%20to%20O1%3B%20you%20should%20automatically%20get%20Feb%2C%20Mar%2C%20...%2C%20Dec.%3C%2FP%3E%0A%3CP%3EIn%20D2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24A%242%3A%24A%24318%2CRANK(INDEX(%24B%242%3A%24B%24318%2C26*(COLUMN(D2)-COLUMN(%24D%242))%2BROW(D2)-ROW(%24D%242)%2B1)%2C%24B%242%3A%24B%24318))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20row%2027%2C%20then%20to%20the%20right%20to%20column%20O%20(or%20vice%20versa).%3C%2FP%3E%0A%3CP%3EThe%20random%20sample%20will%20be%20updated%20each%20time%20Excel%20recalculates.%20If%20you%20want%20to%20%22fix%22%20a%20sample%2C%20select%20the%20range%2C%20copy%20it%20and%20paste%20it%20elsewhere%20as%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871712%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871712%22%20slang%3D%22en-US%22%3EI%20failed%20to%20mention%20that%20these%20items%20are%20electric%20in%20nature%20are%20on%20a%20sheet%20named%20%22Electric%22.%20So%20this%20formula%20needs%20to%20pull%20the%20information%20onto%20%22Sheet1%22%20until%20I%20come%20up%20with%20a%20different%20name.%20So%20where%20do%20I%20insert%20Sheet1%3F%3C%2FLINGO-BODY%3E
Contributor

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?

7 Replies

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

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?
Also, the items are in column E starting in E3:E321. The sheet itself has data all over it from A3:CO322. All I to be concerned with however are the items in column E.

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

ROW 2 is a Header Column if that makes a difference. The data starts in ROW 3
best response confirmed by Carl_61 (Contributor)
Solution

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

Ok Great. It pulled them over to sheet1. thanks