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