Forum Discussion
Please can any one help with a macro?
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.
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.
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
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!)
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
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.