VBA Macro - pull two data sets, in the same column, to a separate spreadsheet

[twmills]

Hello,

In the attachment on the Info tab, I'm looking to pull the Account Number (column A) - in this example the number begins with "5200" - and paste it to the Final tab in column C (starting in cell C6 and going down). Then I need to pull the DLR A/C number, also listed in column A on the Info tab. Then have that pasted and copied down starting on A6 on the Final tab.

On the final tab, I have it listed as it should look after the macro is ran. Don't worry about the other columns in the Final tab, I can figure out that once I get the Account Numbers and Dealer Account Numbers all in there. Also it's worth noting that not every Account Number has a corresponding Dealer Account Number....in this cases a cell should be left blank.

The example only goes down 120 lines or so, when in reality the actual file fluctuates from 1500 - 2000 lines. I'm looking for a macro that will support all the rows of data, that may very from file to file.

Thanks so much in advance.

P.S. If this looks familiar I had a very similar request a month or so ago. But the data formatting has changed to where it doesn't fit the macro I was previously given. This format should be accurate going forward (at least I was told that.)

27449

[p45cal]

See attached and button on Final sheet.

[twmills]

See attached and button on Final sheet.

Works beautifully. Thanks so much!!

[twmills]

See attached and button on Final sheet.

Was testing this out on larger examples that we use, and I noticed the Power Query is removing rows where the Customer name is the same on different different accounts. I added an example of that to your spreadsheet you sent me.

There's three Michael Marn on three different accounts (highlighted yellow on the Info tab), but the query is only returning one of them (as seen on the Final tab). Is there a way in the Grouping to include all the accounts, even if they have the same name?

Other than that, it works great.

[p45cal]

try attached.
It was grouping similarly named accounts that were next to each other; if there were similarly named customers but not next to each other, it didn't group them.

[twmills]

try attached.
It was grouping similarly named accounts that were next to each other; if there were similarly named customers but not next to each other, it didn't group them.

That did the trick. Everything looks great. Thanks again!!