I was looking for something that would just populate and update itself as
soon as the latest weeks data has been dropped in to the worksheet

Try this formulas play which achieves it dynamically
Assume your source data as posted is in Sheet1, cols A to D, data from row 2
down
In another sheet, place in
A2:
=IF(Sheet1!D2="","",IF(COUNTIF(Sheet1!D$2:D2,Sheet 1!D2)1,"",ROW()))
B2:
=INDEX(Sheet1!D:D,SMALL(A:A,ROWS($1:1)))
C2:
=IF(ISERROR(B2),"",COUNTIF(Sheet1!D:D,B2)-ROW()/10^10)
D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(B:B,MATCH(LARGE (C:C,ROWS($1:1)),C:C,0)))
E2:
=IF(D2="","",COUNTIF(Sheet1!D:D,D2))
Copy A2:E2 down to cover the max expected extent of source data, say down to
E100. Minimize/hide cols A to C. Col D will return the full list of "round"
in descending order by their frequency counts (the counts are returned in col
E). Read off the top xx as desired. Ties if any, will be returned in the same
relative order that they appear in the source. The set-up will be dynamic to
new data in the source. Success? celebrate it, hit the YES below
--
Max
Singapore
---