• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic array allocation

M

meetarnav

New Member
Hi - I play chess for the Bristol league and unfortunately the league still use a rudimentary excel sheet to report progress.
1. The sheet can be downloaded from https://www.chessit.co.uk/ - it's called Update.xls
2. If you go to the tab saying "Individual results" you will see that each match is a similar looking table with either 4, 5, or 6 games (players) in each match.
3. I want to be able to create an array that references each table so that I can then search for each match on the basis of (say) player name just by querying that array.
4. Please don't tell me to manually create arrays for sooooo many tables!!!!! It would be useful not to use VBA if I can help it!

Many Thanks,
 

Attachments

  • Update.xls
    546 KB · Views: 13
Last edited:
Hi - Better than an array is to use the Excel inner stuff like its search feature aka the VBA Range.Find method.​
If you really want an array so elaborate at least how it should be …​
​
 
Your file is and Excel 97-2003 file version (.xls); are you using a more up-to-date version yourself? If so which version? (There are things we can take advantage of in the more recent versions of Excel.)

Note that there are some errors at cells AW5 & AE461 of the Individual Results sheet which you might want to correct.
Also there are some peculiar values in cells AA3:AA13 of the Club Stats AH sheet.
 
Marc L said:
Hi - Better than an array is to use the Excel inner stuff like its search feature aka the VBA Range.Find method.​
If you really want an array so elaborate at least how it should be …​
​
Click to expand...
So I want to be able to type in a person's name in a cell and the result should be the names of the people that person has played against and the match points.
 
So nothing to do with an array … You can use the Range.FInd VBA method.​
​
If you need further help, attach an expected result workbook according to a before workbook …​
​
 
meetarnav said:
4. Please don't tell me to manually create arrays for sooooo many tables!!!!! It would be useful not to use VBA if I can help it!
Click to expand...
For this task it is very difficult to create such an 'array', let's call it a table, manually; you already know this. By the same token it would be very hard work to do it with just formulae; the formulae would be cumbersome and difficult to maintain.
Since you've posted this question in the VBA section I presume that a vba solution would be, nonetheless, acceptable.
In the attached, I've added a macro which can be run on the click of the button at the top left of sheet Individual Results.
It works on whichever sheet is the active sheet (because the button is on that sheet, then clearly that's the active sheet).
When it's finished, you'll find a new sheet as the last tab in the workbook with a table (it's not a full blown Excel Table (a ListObject) but it's left autofiltered.
Each row is a game. The column Player contains all the players, both home and away, so you'll find that each game is represented twice in the table, that way if you filter the Player column for a single name you'll get all the games that that player has played, both home and away.
I've arranged for a new window of the same spreadsheet to be created which will allow you to place them side by side; one showing the new table, the other showing the Individual Results sheet. Now you'll be able to filter the table any way you want and then click on the links in the table.
The links in the Player column will take you to the other window and select that player's name in the specific game. The same applies if you click on the opponent's name. If you click on the link in the Date column, it will take you to the top left cell of that match.
You can take advantage of Slicers if you convert the file from a .xls file (it will open in compatibility mode by default) to a .xlsm file. If you convert the new table to a proper Excel table you'll be able to add slicers and as you make filter choices in the slicers or in the dropdowns in the table itself, the other slicers will update themselves and show relevant choices at the top of each slicer's list.
There's nothing stopping you from creating a pivot from this table, but the pivot table won't have links.

Since it looks as if you download the file from time to time to get the most current version, those files won't have any macros in, so you might want to put the macro in the PERSONAL.XLSB file so that it's always available. If you don't know how to do this I can give you instructions.

63682
 

Attachments

  • Chandoo42898Update_02.xls
    574.5 KB · Views: 4
Brilliant, thanks! I will have a look once I get home today - would be great to have those instructions for the Personal.xlsb file - I never heard of it before!
 
You must log in or register to reply here.
Back
Top