here are 2 questions in the shared Google Sheet file

For Q1, you can provide the answer directly in the file.
For Q2, please feel free to use VBA/Python/Excel/Google Sheet to solve
it. If you use VBA/Python, please send us the script.