Locate a set value and copy the value in the cell that's below it

[p45cal]

In the last thread you wanted DLR A/C numbers, now you want the other number; is something like this of interest?:
2020-09-29_123709.png
where both corresponding account numbers are on each row?

In the attached is a table at cell A1 of the Final sheet associated with a (Power) query.
As a demo I've left that table incomplete, but just clicking the button at cell C1 of the same sheet will update that table. The button runs a short 4 line macro.
Now you should update the data in the Shareholders sheet, then click the button on the final sheet and you should have all the account numbers.
The power query currently relies on the account number which isn't the DLR acc. no., to be a pure number (no alpha characters in it) for it to work properly… this can be tweaked.

As an aside, it looks very much like this data comes from a print file created by some other application. Not ideal. So 2 questions:
1. If you have access to that file we could query that file directly more robustly - so attach one here if you can.
2. Whatever application is creating this print file, can it also export data in a more friendly format? This would be much better to process reliably.