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

[twmills]

Yes, the DLR A/C# will be in column A (starting in A6 and going down) on the Final tab, then the corresponding Account # will be in column C (starting in C6). Something else will go in column B, but that's another issue all together.

Unfortunately the data comes from another source, and I don't have access to change the format.....which is a big part of the problem. Which is the main reason why I created an account on this site, to seek some help from those who know a lot more than me .

I tried the macro and it only returned the first two account numbers (65436453653 and 6543654356) and just the first DLR A/C # (Q3Y401538).

Let me know if I need to change the macro you provided in anyway to have it pull all the DLR A/C #s to column A, and have all the corresponding Account Numbers to column C.

I really appreciate your help on this.

Thanks!

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.