Forum Discussion
Formula
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.
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?
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
- 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.
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.
