admin管理员组

文章数量:1242841

I have the following sheet

My goal is to return data in col P randomly from col N or from col O. If cell B3 contains "2 shifts", it should return from col N and if cell B3 contains "3 shifts", it should return from col O. The amount of random cells to be returned is in D3 and should be able to be changed as wanted. I can extract cells in order by using the below.

=ArrayFormula(QUERY({N2:N, RANDARRAY(ROWS(N2:N))}, "SELECT Col1 LIMIT "&$D3))

The above only returns the first 6 but only contains 1 criteria. I cannot make it work. Please help PS: I can only use formulas and not scripts.

I have the following sheet

My goal is to return data in col P randomly from col N or from col O. If cell B3 contains "2 shifts", it should return from col N and if cell B3 contains "3 shifts", it should return from col O. The amount of random cells to be returned is in D3 and should be able to be changed as wanted. I can extract cells in order by using the below.

=ArrayFormula(QUERY({N2:N, RANDARRAY(ROWS(N2:N))}, "SELECT Col1 LIMIT "&$D3))

The above only returns the first 6 but only contains 1 criteria. I cannot make it work. Please help PS: I can only use formulas and not scripts.

  • google-sheets
  • google-sheets-formula
Share Improve this question edited 11 hours ago Patsytalk 9751 gold badge1 silver badge15 bronze badges asked Feb 17 at 12:36 JeromeJerome 738 bronze badges 2
  • Please do not upload images of code/data/errors. – TheMaster Commented Feb 17 at 15:39
  • Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Feb 17 at 15:39
Add a comment  | 

1 Answer 1

Reset to default 1

Try:

=LET(
  arr, TOCOL({IF(B3 = "2 shifts", N2:N, O2:O)}, 1),
  SORTN(arr, D3, , RANDARRAY(ROWS(arr)), )
 )

本文标签： google sheetsExtract randomly data according to multiple criteriaStack Overflow

版权声明：本文标题：google sheets - Extract randomly data according to multiple criteria - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1740158980a2234071.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。