SOLVED

# How to copy a certain number of cells based on a value?

Occasional Contributor

# How to copy a certain number of cells based on a value?

Hello!

I have 6 columns of timestamps, with numerous rows, if I want to only copy (by random selection) the number of cells in each row, by a given number (in column 7), pasting the data starting in column 8, what IF formula could I use?

Here is a photo of what I mean.  https://ibb.co/YD1httX

 Count 8:45 AM 9:51 AM 10:58 AM 3:27 PM 5:25 PM 8:23 PM 2 8:45 AM 9:51 AM 1:42 AM 12:30 PM 8:19 PM 8:30 PM 8:48 PM 8:59 PM 1 8:48 PM 3:57 AM 5:26 AM 6:08 AM 8:31 PM 10:12 PM 11:13 PM 5 5:26 AM 6:08 AM 8:31 PM 10:12 PM 11:13 PM 1:31 AM 4:34 AM 6:15 PM 7:05 PM 8:05 PM 8:28 PM 3 6:15 PM 7:05 PM 8:05 PM 2:33 AM 2:49 AM 9:43 AM 11:20 AM 3:52 PM 9:56 PM 4 2:49 AM 9:43 AM 11:20 AM 3:52 PM
3 Replies
best response confirmed by bryen79 (Occasional Contributor)
Solution

# Re: How to copy a certain number of cells based on a value?

See the attached demo workbook. It uses dynamic array formulas, available in Microsoft 365 and Office 2021.

# Re: How to copy a certain number of cells based on a value?

@bryen79  Alternate without helper cells

``````=LET(in,A8:F8,
n,G8,
count_in,COLUMNS(in),
rand_in,SORTBY(in,RANDARRAY(1,count_in)),
TRANSPOSE(SORT(INDEX(rand_in,SEQUENCE(n)))))``````

The above formula is based on data on row 8

# Re: How to copy a certain number of cells based on a value?

Thanks to both of you for your responses!! I am so grateful! You've saved me a ton of time!!! Both answers are great! I marked the one that is the easiest for me to use but wanted to point out that both are great! Thanks again!!!!