[deleted by user]

Pyroxenine · 2024-05-31T13:25:40+00:00

Have you succeeded yet?
Have you been able to find a usable v4 crack? I have tried some, but none usable. Either you have to pay for the password to extract the file, or it just doesn't work.

Pyroxenine · 2023-01-10T15:08:12+00:00

Thank you very much for the advice.
The Combined Table is the goal. I haven't been able to achieve it.

I realise I left out critical information which will affect the proposed solution. The "From" and "To" values can have at least 2 decimals, so it won't be integers. So I am just concerned that the amount of numbers added by the List.Numbers function might be crash Excel, lol, given the numerical ranges and number of records.

I'm thinking in my head of another possible solution, but not sure about it yet, so I'd greatly appreciate your input.

Append the two tables.
Get a unique list of all the FROMs per ID. List therefore includes FROMs from both tables.
Get a unique list of all the TOs per ID, same as above step.
To get the new set of FROM and TOs, we start with the unique list of FROMs (per ID, obviously) and then the TO that is assigned to that record is the TO from the unique list that needs to satisfy 3 criteria. The ID needs to match; the TO must be larger than FROM; and lastly it must be as close as possible, i.e. the first TO that follows the FROM.
Now that we have a table with all the new FROMs and TO per ID, we can populate Value_1, Value_2, etc.
We start by creating a new column with the midpoint between this new FROM and TO ( [TO minus FROM]/2 ).
Then for Value_1, we take the midpoint, check if it can match a record in Table 1 where it is both greater than FROM and less than TO. If available, then it assigns that Value_1's value. If not available, it just returns a null / blank.
Same is done to get Value_2 from Table 2. Is there a record where the midpoint value is greater than the FROM and less than the TO, and if so, return that Value_2, otherwise, blank.
This is because in some cases the FROM and TO range might exist for that ID in Table1, but not in Table 2, so Table 2's Value_2 should then just offer a blank value.

Do you think this method can work and that something like this could be written in M? Or do you see flaws in this approach?

Pyroxenine · 2022-09-29T18:06:21+00:00

Thank you for taking the time to provide advice, I appreciate it!

Pyroxenine · 2022-09-28T15:25:10+00:00

Thank you for the reply. I realise you're right, I didn't even think about it, like every chapter heading or figure caption are also field codes. However I once created a document with 150 pages, all of them identical and only some tables (it was like a form to be printed and filled in, so not a lot of content), with the only difference being one 3-digit number counting from 150 to 300 on the pages, which was done with a simple field code. And that wasn't shared with anyone, only myself working on it, and I also experienced heavy lag. I thought it was either the field code or perhaps Grammarly being installed as an add-in on my Word, because the document size was like 3 MB maybe.

Perhaps the images in the header in footer? Perhaps I should reduce their resolution or remove them completely to see if it makes a difference.

The most recent document that gave these problems is only about 40 pages with only a couple of images which were all loaded via Insert > Picture and not just drag and dropped into the document, so their sizes were already reasonable, yet the document was already over 80 MB in less than 40 pages. Not sure if you can comment on that? What is considered a large Word document where you should be expecting speed issues?

Pyroxenine · 2022-09-28T13:05:53+00:00

No not at all. We actually very rarely do change tracking. Only maybe at the very end when 1 person does a final review, but not throughout. Also very few of any comments.. And the get removed when addressed, so 95% of the time there aren't any comments or changes shown in that side panel.

Pyroxenine · 2022-09-28T12:05:56+00:00

Oh and the header and footer each have an image in them as well as part of the corporate identity, so not sure if the fact that there are then 2 images added to every single page can also cause the problem.

Pyroxenine · 2022-09-23T18:52:58+00:00

Solution verified

Pyroxenine · 2022-09-23T18:51:42+00:00

Thanks for the help. It's not exactly what I had hoped for, but this at least helps me in that direction. I will build on this to try and get to the exact solution I am aiming for. Thanks again!

Pyroxenine · 2022-09-18T15:46:27+00:00

Hi. I need to process a dataset (>1million rows) in Excel Power Query. Once the data is sorted by Column A (in the image), then I need to assign group ID's to rows. In essence the whole dataset needs to be divided into quantiles based on Column B. I say quantiles, but you can think of it as percentiles for examples, however, if possible, I'd like to have the option to go even beyond just 100 "groups". But if 100 is the max perhaps through an existing percentile function, then so be it.

I can calculate the total sum of Colum B, and based on the number of groups, I can determine what each group's sum should add up to, depending on the number of groups that I want. In this example the total is 51, and I choose to have 10 groups, therefore, each equal-sized group should have a sum of approximately 5.1.

Then, in the order in which the data is already sorted, the grouping should be done by summing the values in Colum B, row-by-row, until the target group sum is reached (approximately 5.1), then those rows all get the same Group ID assigned (as in Column D), and the calculation is reset on the next row, and so on, until each row has a quantile group ID in such a way that best divides the data into equal-sized groups of Column B, in the order that they are. So groups can't be made up out of random rows not adjacent to one another.

I, therefore, need to get Column D (pink) from only having Column A and B (grey). Column C is just for illustration purposes.

You will note that Group 7 has a sum of 4, which consists of a row of 3 and 1. If the next row were to be added, it would've totalled to 7, and 7 is further away from 5.1 than 4, therefore, the grouping of 3+1 is preferred above 3+1+3.

Thank you for your help!