SOLVED

# 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?

7 Replies

# Re: Formula

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.

# Re: Formula

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?

# Re: Formula

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.

# Re: Formula

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.

# Re: Formula

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

# Re: 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.

# Re: Formula

Ok Great. It pulled them over to sheet1. thanks