Macro to pull data from one sheet to another based on higher/last number used

[JJ77]

Hi,

I'd like some help to create an easier way to pull specific rows from a sheet to another sheet based on the higher number of a client record.
My tables/reports are huge and usually have over then thousand rows and more than twenty columns too and copy/paste/filter/delete etc.... takes forever.
If you can help, please see attached excel sample.

Thanks
Jake

[Fluff13]

How about

Please Login or Register  to view this content.

[xladept]

Or this:

Please Login or Register  to view this content.

[MarvinP]

Hi JJ,

In a newer version of Excel there is a newer tool called Power Query, or Get & Transform. It can do all of its processes in memory and very fast. If you had a newer version of Excel it is about 10 mouse clicks to do your problem. Here is the Power Query Code and attached file showing what the result is. Time to upgrade to Excel 365?

Please Login or Register  to view this content.

Note: I didn't need to type any of the above code as it was created using mouse clicks and the PQ editor.

Copy Highest using PQ.xlsx

To have my solution above to work correctly, as Orrin has pointed out it needs some work. It is wrong as given above. BUT if you sort the top table by the first column using Descending (big values to the top) SAVE THE FILE TO DISK and then refresh the query it agrees with the other answers. Power Query works in memory from looking at the file that is stored on the disk/cloud. This seems to be what makes it faster. (?) Just sorting it using Descending within the Power Query editor isn't enough!! I wonder if this is ok for the OP?

[JJ77]

Thanks.
It Looks like is working, but I'm trying to figure out how to add into my real data since I don't know much about the coding I see .
I put to test in the excel sample I attached and it missed the last yellow highlight.
In my data, the info are in different columns than the sample, like Client Number may be in column CV and Record in column D.
Where to I change to check the correct columns ?
Also, how to I keep the header when it copies to Sheet 2 ?

Thanks

[Fluff13]

The code I supplied missed the last highlighted rows because they are the same Client Number as the first row, which has a higher record number.
If I've misunderstood you, try the code from xladept which may better meet you needs, or alternatively the suggestion from MarvinP

[xladept]

Here's a commented routine with column variables to be entered by you for the "real data" - I've also included a short function to find the column numbers fr CV is column#100:

Please Login or Register  to view this content.

[MarvinP]

Hi JJ,

Any chance you can upgrade your version of Excel and get the newer tools?