Is there a good aggregate equivalent for Google Sheets with the large function?

Question

I try to write an easy randomizer based on a database for randomly combining data. This works fine in Excel, but not in Google Sheets, because there is no aggregat

I've already tried LARGE and MAX but then it's not randomizing, and with SUBTOTAL it's not working. I thought it also could be based on ROW, but maybe I have a wrong concept.

The code in excel is like this (i use the German version of Excel, but this should be the English equivalent)

=TEXTJOIN(" ";TRUE;INDIRECT("A"&ROUNDUP(RAND()*AGGREGAT(14;4;(A:A<>"")*ROW(A:A);1);0));INDIRECT("B"&ROUNDUP(RAND()*AGGREGAT(14;4;(B:B<>"")*ROW(B:B);1);0));INDIRECT("C"&ROUNDUP(RAND()*AGGREGAT(14;4;(C:C<>"")*ROW(C:C);1);0)))

As I mentioned there is no aggregat, and the other solutions I looked up didn't work for me.

Here is a document where you can look it up https://docs.google.com/spreadsheets/d/1USg9VCNgjYgz_THlJaC7tWcFnjgIuVyPyAHqw5gL8cI/edit?usp=sharing The expected output should be a randomised sentence (here in german) — Benjamin Bertram, May 24 '19 at 13:42
do you want a random cell to be picked up from any of those columns? (A, B, C) is that what you are after? — player0, May 24 '19 at 19:17
so you want to pick 3 random cells from 3 columns and create a sentence? — player0, May 25 '19 at 14:53
I want to pic one random cell in each column, To get one full sentence made of one random word of each column. — Benjamin Bertram, May 25 '19 at 18:00
That did it perfectly. Thanks! It is also way more effecient than my previous atempt. Thank you very much! — Benjamin Bertram, May 26 '19 at 20:37

player0 · Accepted Answer · 2019-05-25T19:22:05.720

in Google Sheets, there are multiple formulae that can mimic AGGREGATE such as QUERY, FILTER, VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH and depends on a task which of these mentioned suits best.

=INDEX(INDIRECT("A1:A"&COUNTA(A1:A)); RANDBETWEEN(1; COUNTA(A1:A)))

=INDEX({INDIRECT("A1:A"&COUNTA(A1:A))\
        INDIRECT("B1:B"&COUNTA(B1:B))\
        INDIRECT("C1:C"&COUNTA(C1:C))}; RANDBETWEEN(1; COUNTA(A1:A)))

={INDEX(INDIRECT("A1:A"&COUNTA(A1:A)); RANDBETWEEN(1; COUNTA(A1:A)))\
  INDEX(INDIRECT("B1:B"&COUNTA(B1:B)); RANDBETWEEN(1; COUNTA(B1:B)))\
  INDEX(INDIRECT("C1:C"&COUNTA(C1:C)); RANDBETWEEN(1; COUNTA(C1:C)))}

=INDEX({INDIRECT("A1:A"&COUNTA(A1:A));
            INDIRECT("B1:B"&COUNTA(B1:B));
            INDIRECT("C1:C"&COUNTA(C1:C))}; RANDBETWEEN(1; COUNTA(A1:C)))

=JOIN(" "; {PROPER(
 INDEX(INDIRECT("A1:A"&COUNTA(A1:A)); RANDBETWEEN(1; COUNTA(A1:A))))\
 INDEX(INDIRECT("B1:B"&COUNTA(B1:B)); RANDBETWEEN(1; COUNTA(B1:B)))\
 INDEX(INDIRECT("C1:C"&COUNTA(C1:C)); RANDBETWEEN(1; COUNTA(C1:C)))})&"."

Is there a good aggregate equivalent for Google Sheets with the large function?

1 Answers1