Split table for quicker searching and sorting??

[zozew]

Hello masters of excel!

I need advice...

I have a data set or a table with about 6000 rows and about 20 columns of text and another 15 or so with some formulas splitting some strings then DATEDIF and then converting to years and months a few times over.. So.. Not a huge amount of of data but still enough so that when I add a column to the table it takes about 20s on my old old AMD A8 desktop.. And if I sort a columns it takes about 10s

So to the question, I've been researching, asking chatgpt and then researching and testing and come to the point where I need some human touch to this question..

Is there a way to split the table with the simple text columns as I will use them 90% of the time and be able to sort and search and move a round easier with the formula columns in a another sheet? Either dynamic referencing, or pivot tables or some type of copying?

This master table will get new rows added to it and removed a few times a day.. Not a huge amount.. But still..

Basically it's a data set of people and personal info and then a bunch of calculation with dates and numbers using column formulas..

Cheers any help appreciated 


Edit:
I'm adding a link to the same query in another forum
https://www.mrexcel.com/board/thread.../#post-6142573

Sorry for missing this step

[DJunqueira]

You could Power Query to eliminate some formulas or to copy to another sheet, actualizing it whenever necessary by a click of a button.
Some functions also could be responsible for the delay also, specially INDIRECT().
For more ideas attach a sample file with your formulas and a representative set of filtered data, clean from any relevant information.

[zozew]

You could Power Query to eliminate some formulas or to copy to another sheet, actualizing it whenever necessary by a click of a button.
Some functions also could be responsible for the delay also, specially INDIRECT().
For more ideas attach a sample file with your formulas and a representative set of filtered data, clean from any relevant information.

I'm on excel 2013,but I think I have power queries as an add on.

I'll try to upload a structure of the sheet it is pretty confidential, but I'll remove column names as well..

The reason for splitting is of course the sorting and speed but also overview of it all. Having so many columns with data and many of them with like random numbers is pretty tough to read...

I'll get back shortly with the example file

Thanks

[zozew]

Here is the example file, there are more text columns but this one contains most of the formulas, a few columns are missing but it will mostly be about half a dozen more doing similar calculations

Cheers

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

[zozew]

Link has been added, sorry for missing this step

[DJunqueira]

Your sample got a little too tiny (1 line)..
I'll try do something with what I see.

This formula is for column P. You substitute the '-' for '- ' in such way that the extra space will ensure that you only get the last number.

Please Login or Register  to view this content.

I separated your 'Y' columns from 'X' columns into 2 separate sheets with Power Query.

[zozew]

Ahh sweet!!

Sorry for the one liner but it's basically the same info with diffrent numbers. I'll give you a little more context, it's about calculating sentences and the served time. This particular calculation is a little weird as there is a good time conduct allowance added according to 4 brackets from a table, this is all in days and then these days are converted to years months and day with yeses as 360 and months as 30 days and then it's added to the actual time the person has spent in prison.

And then depending on certain conditions the start date for the calculation varies.. So what you see is a min to a max sentence and then the served time is compared to the max time to see if it has been reached..

But ill install power query as I'm not sure it's working correctly on my phone now..

But a thousand thanks! I'll get back to you by tomorrow 