Is there a quick way of setting up data tables?

[mrvp]

Hi,

I do various bits of modelling in Excel usually using multiple Data Tables of 10,000 rows in a single worksheet (I like to get fairly stable numbers ). Basically is there a quick way or utility or workaround out there (or even a macro) for setting up data tables in a more efficient manner?

Basically I sort out my first column of 10,000 numbers (1-10,000) for my first data table, copying that column through for however many data tables I have and then I just treat each data table individually and do them as you normally would (it's just with 10,000 lines it seems to take an age ).

I just wondered if anyone had any ideas or workarounds that could speed this up? I've tried reducing the view size, to fit more rows on the screen but then it sometimes becomes more awkward to pick the right cell accurately etc.

Thanks in advance for any suggestions,

[TMS]

One way, with VBA:

Please Login or Register  to view this content.

Regards, TMS

[newdoverman]

If your data has column headers, that is, each column is one field and it has a name or "header" and each row is one record, and there are no blank rows, clicking in the data and then click the Insert Tab, Table and your data table is automatically created for the correct number of rows and columns. Then you can use the power of the TABLE to sort and filter your records.

If you mean something different from this, could you post a small sample of your workbook (not a picture) with what you start with and what you would like to finish with.

[mrvp]

Hi,

Thanks for the advice, I may try the VBA business but do find Macro's a bit scary . Just so you know what I am trying to do in terms of data tables, I have posted a values sheet of one of my models (so it doesn't have the workings in). The data tables are the bit starting in M42 highlighted green, with the data table values in row 43, being fed by the table in purple above it (if that makes sense).

Hope this helps a bit. Basically the model I'm trying to do at the moment is for the season just gone, I've reworked the sheet I have attached and just want to put a data table in for 20 teams that is a bit time consuming (but need the values out of the data tables to try and validate the model).

Example Values_EPL_RevB.xlsx

[TMS]

Please Login or Register  to view this content.

[mrvp]

Please Login or Register  to view this content.

Nice. I can see how that works now (I figured I would have to key it to something). I can see it will be a case of setting it up at the start but the time it will save will be worth it (and the range row can be copied and just flip the reference key at the start so it won't be that time consuming). I'm expecting that it could probably be re-worked even more, to key it off of whatever cell you are over - I'll bounce it around at work during my lunch break (people come to me about functions at work which are never that complicated e.g. IF's and my colleague spends all day doing Macro's). Will check this out after dinner and mark this as solved later as it looks like it will do the job.

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[mrvp]

Hi,

It didn't do quite what I was expecting it to do (and apologies I probably didn't make it clear); I was actually looking for the function of doing the data table itself (if that makes sense - what was suggested in VBA Code would save me time marginally, once I have adjusted for new teams in this case etc. but what I was actually looking to do was automating or shortcutting the highlighting of both columns, highlight all 10,000 cells, data, data table, column input cell etc.). The highlighting 10,000 rows just seems to take an age (especially once the adjacent columns start getting filled up with data). Can this actually be done or short-cutted though? In the case of the attached example I mean highlighting X35:Y10035 (where the first data table would be etc.).

I would have included the code I used below except I saved the file in the wrong format and have just realised that I lost the code (I figured xlsx's held Macro's ).

I have attached an output sheet - values and number formats only, to give you a better idea of what I am trying to do.

The Data Table area starts over at X33 (I've highlighted the top left corner as an indication of where I applied it). Basically I put row 33 in myself (as I may try and use this next year and just wanted a team1, team2 etc. as a key). Row 35 references the predicted finishing position in column AG (e.g. so I want the data table to play out the league finishing position of each team 10,000 times etc. - which it would do). The code added in row 34, and the 1 to 10,000 columns.

I hope this gives a better indication of what I am trying to do.

Thanks,

Model_Example_Values_170514.xlsx

[mrvp]

Hi could someone delete this please - for some reason it posted twice and I have edited the text out.