CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Kay
Kay

Reputation: 345

Aggregating Data in Google Sheets via Formula

I have 100+ rows that are updated daily via import range. My objective is to aggregate user's key results from multiple cells into one single cell per user. Currently I have to update my column "Key Results Aggerated" manually by copying and pasting since my formula doesn't work since it aggregates info incorrectly. Thanks in advance. 🙏

Current formula in Cell E1:

={"Key Results Aggerated";MAP(A2:A,D2:D,LAMBDA(aval,dval,IF(aval="","", TEXTJOIN(CHAR(10),1,dval:INDEX(D:D,ROW(dval)+IFNA(XMATCH("*",INDEX(A:A,ROW(aval)+1):INDEX(A:A, ROWS(D:D))),ROWS(A:A))-1)))))}

Pic: Raw Data

Pic: Results I am trying to achieve

Google Spreadsheet Example

Upvotes: 0

Views: 73

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 29904

you can try:

={"Key Results Aggregated";INDEX(BYROW(IF(LEN(C2:C),COUNTIFS(ROW(C2:C),"<="&ROW(C2:C),C2:C,"<>"),),LAMBDA(z,IF(z="",,TEXTJOIN(CHAR(10),1,IFNA(FILTER(D2:D,SCAN(,IF(LEN(C2:C),1,),LAMBDA(a,c,a+c))=z)))))))}

enter image description here

Upvotes: 1

Mart&#237;n
Mart&#237;n

Reputation: 10117

Seen just some minutes ago your previous question. Add a two in XMATCH for allowing wildcards:

={"Key Results Aggerated";MAP(A2:A,D2:D,LAMBDA(aval,dval,IF(aval="","", TEXTJOIN(CHAR(10),1,dval:INDEX(D:D,ROW(dval)+IFNA(XMATCH("*",INDEX(A:A,ROW(aval)+1):INDEX(A:A, ROWS(D:D)),2),ROWS(A:A))-1)))))}

Upvotes: 1

Related Questions