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

[twmills]

Hello,

This is very similar to situation I received helped on recently. So I'm sorry in advance if this looks familiar to some of you. But I'm looking to search column A for the phrase "Account Number", then have it copy the actual number below it and paste in a separate sheet.

I provided an example spreadsheet of what the raw data appears like (Shareholders) and what the finished product should look like (Final). The heading "Account Number" on the Final sheet will all ready be provided, so that doesn't have to be carried over in the macro. The catch is, in some instances, there's a page break in the raw data preventing the actual numeric number to not be directly below the "Account Number" heading. I provided an example of this in lines 19-25 in the Shareholders tab.

Thanks as always.

[snb]

Please, do not violate Excel.
To introduce pagebreaks Excel has it's own method: use 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.

[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.

[p45cal]

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

That's how left it; I'd removed the results so you could could see it being updated with the current sample data you provided. You did click the button (having enabled macros, of course) and waited a second or two (Power Query often takes a little longer at the first update)? I just checked it again by downloading the attachment from this site and it works fine, bringing in all 6 pairs of numbers.

So let's have a peep at this source file, it should be easier to grab the data you need directly from the file without having to bring it into Excel first. It makes handling new versions of the file easier too.

[twmills]

That's how left it; I'd removed the results so you could could see it being updated with the current sample data you provided. You did click the button (having enabled macros, of course) and waited a second or two (Power Query often takes a little longer at the first update)? I just checked it again by downloading the attachment from this site and it works fine, bringing in all 6 pairs of numbers.

So let's have a peep at this source file, it should be easier to grab the data you need directly from the file without having to bring it into Excel first. It makes handling new versions of the file easier too.

Now I'm getting a Debug message, like it's not identifying the "ResultTable" Range to refresh. Anyway, attached is the original file in the "Shareholders" tab/workbook. I need the macro to pull the Account Numbers (usually the 10 digit number beginning with 29), and have all the account numbers placed below "Account Number" (column C) in the Final tab.

As always, thanks for your help!

[p45cal]

In the attached is a button near cell A2 of the Final sheet which runs the macro blah which does the same thing as before (refreshes the query table at L5 of the Final sheet) plus makes copies of the columns to your columns A and C.
Be aware that I don't delete any previous data in your table, only overwrite, so if the lengths of the tables are different or they're in a different order you'll get mis-aligned data, so it's best to delete the whole table (yours) first.
The query table is at L5 of the Final sheet, but it can be anywhere in the document (perhaps give it its own hidden sheet?).
For comparison, there's a copy of the output of Bob Phillip's macro CopyDealerAccountNumber. They seem to be exactly the same.

Your account numbers in column C are all numeric (no alpha characters at all); will they always be so? If not, my query might need a tweak.

[twmills]

In the attached is a button near cell A2 of the Final sheet which runs the macro blah which does the same thing as before (refreshes the query table at L5 of the Final sheet) plus makes copies of the columns to your columns A and C.
Be aware that I don't delete any previous data in your table, only overwrite, so if the lengths of the tables are different or they're in a different order you'll get mis-aligned data, so it's best to delete the whole table (yours) first.
The query table is at L5 of the Final sheet, but it can be anywhere in the document (perhaps give it its own hidden sheet?).
For comparison, there's a copy of the output of Bob Phillip's macro CopyDealerAccountNumber. They seem to be exactly the same.

Your account numbers in column C are all numeric (no alpha characters at all); will they always be so? If not, my query might need a tweak.

So what might cause it to debug right here?

.QueryTable.Refresh BackgroundQuery:=False

With the message:

"Run-time '1004':
[Expression.Error] 5 arguments were passed to a function which expects between 2 and 4."

There's some sort of disconnect with my excel trying to "Refresh" the table. Should I be doing something to the file before running the BLAH macro? I noticed in Name Manager the ResultTable seems locked or greyed out...I don't have access to edit it. Even if I do a simple right click on the ResultTable and select Refresh, that same Expression.Error message comes up for me.

Also, the length of the file with the Account numbers I'm guessing will vary from time to time....it won't always be the same.

Thanks for your patience on walking me through this.

[p45cal]

So it fails right out of the box?
I'm thinking this is a Power Query version compatibility problem.
Can you go into Power Query Editor and Refresh Preview in the SceData query and see if it errors, if so starting from the first step run down the steps until you get the error and tell which step that is?

[twmills]

So it fails right out of the box?
I'm thinking this is a Power Query version compatibility problem.
Can you go into Power Query Editor and Refresh Preview in the SceData query and see if it errors, if so starting from the first step run down the steps until you get the error and tell which step that is?

Yeah, I do get a compatibility warning when going into the query editor. The attached error message is what I'm get, in the Added Index step I think?

[p45cal]

Change:
= Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
to:
= Table.AddIndexColumn(Source, "Index", 0, 1)

or update your Power Query.

[twmills]

Yeah, I do get a compatibility warning when going into the query editor. The attached error message is what I'm get, in the Added Index step I think?

wow, thanks so much. That seems to work!

Now what should I change if there's 5000 lines of shareholders, rather than just 500...like in the example you put together for me?

Much appreciated!!

[p45cal]

Now what should I change if there's 5000 lines of shareholders, rather than just 500...like in the example you put together for me?

Absolutely nothing.

[twmills]

Absolutely nothing.

Great, you da man.

Thanks again for helping me out.