Find maximum used word on the basis of other column in Google Sheets-CodePudding

I want to filter some data according to last name aur most used word in the Names on the basis of ID number. And want this outcome by only formulas. please suggest only formulas. below is the data and required result for your reference.

enter image description here

CodePudding user response：

use:

=INDEX(ARRAY_CONSTRAIN(SORTN(QUERY(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B; " ")); "×"); 
 "select Col1,Col2,count(Col2) 
  where Col2 is not null 
  group by Col1,Col2 
  order by count(Col2) desc
  label count(Col2)''"); 9^9; 2; 1; 1); 9^9; 2))