Search code examples
randomgoogle-sheetsaggregategoogle-sheets-formulatextjoin

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

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.

Solution

  • 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)))

    0

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

    0

    ={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)))}

    3

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

    4

    =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)))})&"."

    0