Please can any one help with a macro?

[harwood66]

Basically I want to enter a team name in I1 (Milan eg). I then need Col H to be searched for the last 50 times "Milan" appears. I then just want COLS D E F G to be auto copied for me to paste elsewhere. Many thanks if anyone could help me with this.   Kind regards Dave

 

[#2]

@harwood66 Is this something you need to do very often? If not, consider using SEARCH to identify all rows that contain the search item from I1. Filter column I manually, copy and paste values from B:G. Or, if you are in the most recent Excel version that supports the FILTER function, you can further automate the process. Attached just a simplified example of what is possible without macro's.

[#3]

Hi @Riny_van_Eekelen . Thanks for your time. I currently search col H, then copy and past the last 50 occurrences in columns DEFG. I do this upto 100 times a week and is a bit of a long task. Was just wondering if it could be automated/speeded up?  Kind regards Dave

[#4]

@harwood66 That sounds more like a job for "Get & Transform Data" a.k.a. PowerQuery (PQ). If you could upload a representative file, I or someone else out here, could guide you into creating a tool. I imagine you would type a club name in one cell and then press "Refresh" and PQ will spit out the top 50 list in a couple of seconds. (Provided you are not on a Mac!)

[#5]

Hi @Riny_van_Eekelen   your help with this is much appreciated. I have attached a version of the file as requested. Basically I want to type a team in Col I4 and then get it to  copy and paste the last 50 appearances that the team appears in COLS D and E . The four cols I want to copy are DEFG so the cell count is 200. When copied I would simply paste the 200 cells into the next process. Thanks again Dave

[#6]

@harwood66 Have a look at the attached workbook. Change the team name in the yellow cell (a Named Range called "SelectedTeam") on the "Data tab".

On the Data ribbon, press "Refresh All".

and go to the Top50 tab where you can pick-up the top 50 table for the selected team.

Now, the trick is that as long as your Data is stored in a structured table (and in this case called "Table1"). the refresh should work over-and-over-again. By selecting "Queries & Connections" you will be able to access the fairly simple queries that make it all possible. Try and see is this is a workable solution for you.

[#7]

@Riny_van_Eekelen    Wow that is absolutely incredible to me. Am I able to update the "Data" table as per usual? I normally update from the bottom and then flip latest to the top. Cannot thank you enough you are so kind.  Regards Dave

[#8 · Accepted solution]

@harwood66 Just give it a try. As long as you keep the structured table called "Table1". No need to pre-sort. The query does that for you. Come back if you encounter any problems.

[#9]

@Riny_van_Eekelen   Absolutely brilliant, you have saved me several hours a week. I am going to have a look at this PQ online, and try and pick it up. Kind regards Dave