Jun 30 2020 09:30 PM
Jun 30 2020 09:30 PM
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
Jun 30 2020 10:25 PM
@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.
Jul 02 2020 04:59 AM
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
Jul 02 2020 06:56 AM - edited Jul 02 2020 06:59 AM
@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!)
Jul 03 2020 02:58 AM
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
Jul 03 2020 04:54 AM
@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.