You could
try and test this incomprehensible formula in the single cell M7:
Code:
=BYROW(A7:A1130,LAMBDA(c,IF(LEN(TRIM(c))>0,SUMPRODUCT((SCAN("",A7:A1130,LAMBDA(a,b,IF(b="",a,b)))=c)*(LEN(TRIM(C7:C1130))>0)),"")))
See attached.
Named range not required, no
Power Query query.
Edit: Added the equivalent
named lambda formula in cell N7.
2024-08-02_170342.jpg
If you want to transfer this
named lambda function to your own workbook, an easy way is to copy my worksheet to your workbook then immediately delete it from your workbook; the function should remain.