1

I have a question and need your help. Thanks in advance.

I can find the 2nd most frequently occurring text value via using SQL Query but I need to find it without SQL. (based on some conditions) / Answer 2 (new) sheet

Here is the Test Sheet https://docs.google.com/spreadsheets/d/14aW9OMWh6AbS0Y05LKEg9L6P2uT9elxD/edit#gid=2144114460

PS: You can Edit the sheets.

player0
  • 124,011
  • 12
  • 67
  • 124
pythoner
  • 25
  • 4

1 Answers1

2
=MIN('Answer 1'!$F$2:$F$15)

equals to 20 and only Hans got that

what you need is:

=INDEX(IFNA(VLOOKUP(A2:A5; 
 SORT({'Answer 1'!B2:B15\ 'Answer 1'!A2:A15\ 'Answer 1'!F2:F15}; 3; 1); {2\ 3}; 0)))

enter image description here

and try:

=INDEX(LAMBDA(y; FILTER(y; COUNTIFS(INDEX(y;;1); INDEX(y;;1); 
 SEQUENCE(COUNTA(INDEX(y;;1))); "<="&SEQUENCE(COUNTA(INDEX(y;;1))))=2))
 (SORT(SPLIT(FLATTEN(LAMBDA(x; INDEX(QUERY(x; "select Col2,Col3,Col4"; 1); 1)&"×"&
 INDEX(x;;1)&"×"&QUERY(x; "select Col2,Col3,Col4"; ))
 (QUERY({'Raw Data'!B2:B\'Raw Data'!E2:E}; 
 "select Col2,count(Col2) where not Col2 matches '^$|N/A' 
  group by Col2 pivot Col1"))); "×"); 1; 1; 3; 0));;2)

enter image description here

UPDATE - without SQL:

=BYROW(A7:A9; LAMBDA(y; TEXTJOIN(CHAR(10); 1; LAMBDA(z; FILTER(INDEX(z;;2); INDEX(z;;1)=y; INDEX(z;;3)=
 LARGE(INDEX(z;;3); 2 +N("2 for 2nd largest"))))(LAMBDA(x; FILTER(x; INDEX(x;;1)=y))
 (LAMBDA(b; e; SORT(SORTN(SORT({B\ E\ COUNTIFS(E; E; 
 SEQUENCE(COUNTA(E)); "<="&SEQUENCE(COUNTA(E)))\ B&E}; 3; 0); 9^9; 2; 4; 1); 3; 0))
 (FILTER('Raw Data'!B:B; 'Raw Data'!D:D="Fail"; 'Raw Data'!E:E<>"N/A"; 'Raw Data'!B:B=y); 
  FILTER('Raw Data'!E:E; 'Raw Data'!D:D="Fail"; 'Raw Data'!E:E<>"N/A"; 'Raw Data'!B:B=y)))))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks for the solution for Answer 3 sheet. How did I miss this. But for the solution for Answer 2 sheet, i can not use this because you used SQL query. I need a solution without using SQL query. – pythoner Dec 27 '22 at 02:50
  • thank you very much for the formulas. is it possible to explain formulas to me? I never worked with Lambda before. – pythoner Dec 27 '22 at 04:50
  • @pythoner sure. see the "why lambda" part: https://stackoverflow.com/a/74393500/5632629 – player0 Dec 27 '22 at 14:08