Calculate sum and rearrange rows to get value

[Cimpcrro]

I have a table with positive and negative values in the same column and I need to order them so their sum at each row is closest to 0 and I need to do this based on article number.
For example, for article X I have the next order:

Book2
	C	D
1	Item no.	Value
2	111	100
3	111	200
4	111	-50
5	111	-70
6	111	-30
7	111	-40
8	111	-50
9	111	-80
10	111	-100
Sheet1

I need to rearrange it like this:

Book2
	C	D
12	Item no.	Value
13	111	100
14	111	-50
15	111	-70
16	111	200
17	111	-30
18	111	-40
19	111	-50
20	111	-80
21	111	-100
Sheet1

Thank you!

 

[earthworm]

I don't see any pattern or logic in the answered data . You need to elaborate more .
As there can be more than one answers .

 

[Joe4]

I don't understand the logic of your example.
Can you walk us through it?

 

[Cimpcrro]

I don't see any pattern or logic in the answered data . You need to elaborate more .
As there can be more than one answers .

It's a simulation on invetory progress.
First value should be the first positive value (eithter original inventory or first incoming quantity), Following values should be in order the negative values (sales orders). If the inventory (sum of above lines) <=0 we need to insert the next positive value (purchase).
Thanks in advance for your help!

 

[Cimpcrro]

I don't understand the logic of your example.
Can you walk us through it?

It's a simulation on invetory progress. First values should be the first positive value (eithter original inventory or first incoming quantity), Following values should be in order the negative values (sales orders). If the inventory (sum of above lines) <=0 we need to insert the next positive value (purchase).
Thanks in advance for your help!

 

[Joe4]

Is your data actually in an Excel Data Table?
I ask because you mentioned "table", but Excel tables typically look something like this (note the formatting):

Or maybe you are just using the term table "loosely", and you just mean it is data listed down a column?
That answer may effect how to try craft a solution for you.

Also, the only way I can really think of how to do this is to use VBA.
Is that acceptable?

 

[Cimpcrro]

Is your data actually in an Excel Data Table?
I ask because you mentioned "table", but Excel tables typically look something like this (note the formatting):
View attachment 51591

Or maybe you are just using the term table "loosely", and you just mean it is data listed down a column?
That answer may effect how to try craft a solution for you.

Also, the only way I can really think of how to do this is to use VBA.
Is that acceptable?

The data is loaded from power querry into a table. I used a simplified example which was of course not formated as table.
VBA is great!
Thank you!

 

[Joe4]

Hmmm... This is going to be very difficult, especially working with tables (I really hate trying to move around records in a table!).
I am not sure I can think up of a good way to do this.

There are also some details which need to be worked out still, as your simple example doesn't cover a lot of things.
So here are some other questions:

1. How many different item numbers are there in the list?

2. Are they always sorted by this, so all the records form each individual item number will be bunched together?

3. Is the first number for each item always positive?

4. How would you handle a situation like this?

If we start of with 20, adding 10 would actually take us further from 0 (20 + 10 = 30), but if we moved up -100, then the sum becomes negative (20 - 100 = -80), and -80 is further from 0 than 30 is.
So do we move up the -100 or not in this case?

5. Any chance we can work with the numbers in a list, instead of a table?

Depending on your answers to the questions, I might be able to come up with a rather brute force way of doing this, but I cannot make any promises.

 

[Cimpcrro]

Hmmm... This is going to be very difficult, especially working with tables (I really hate trying to move around records in a table!).
I am not sure I can think up of a good way to do this.

There are also some details which need to be worked out still, as your simple example doesn't cover a lot of things.
So here are some other questions:

1. How many different item numbers are there in the list?

2. Are they always sorted by this, so all the records form each individual item number will be bunched together?

3. Is the first number for each item always positive?

4. How would you handle a situation like this?
View attachment 51616

If we start of with 20, adding 10 would actually take us further from 0 (20 + 10 = 30), but if we moved up -100, then the sum becomes negative (20 - 100 = -80), and -80 is further from 0 than 30 is.
So do we move up the -100 or not in this case?

5. Any chance we can work with the numbers in a list, instead of a table?

Depending on your answers to the questions, I might be able to come up with a rather brute force way of doing this, but I cannot make any promises.

Hello,

1. How many different item numbers are there in the list? - Currently the table has ~16.000 lines, and ~ 2.000 Item numbers

2. Are they always sorted by this, so all the records form each individual item number will be bunched together? - Yes, the sorting is done in Power Query, if any other sorting or helping column is required, please let me know.

3. Is the first number for each item always positive? - Yes

4. How would you handle a situation like this?
View attachment 51616

If we start of with 20, adding 10 would actually take us further from 0 (20 + 10 = 30), but if we moved up -100, then the sum becomes negative (20 - 100 = -80), and -80 is further from 0 than 30 is.
So do we move up the -100 or not in this case?

- I would have the following order: 20; -100; 10


5. Any chance we can work with the numbers in a list, instead of a table? - Yes, I can convert the table to list before running the VBA.

Thanks again! It will be a great help for me

 

[Joe4]

4. How would you handle a situation like this?
View attachment 51616

If we start of with 20, adding 10 would actually take us further from 0 (20 + 10 = 30), but if we moved up -100, then the sum becomes negative (20 - 100 = -80), and -80 is further from 0 than 30 is.
So do we move up the -100 or not in this case?

- I would have the following order: 20; -100; 10

Hmmm... If that is the case, the example you posted does NOT follow the logic you explained.

In your expected output, you show 200 in the 4th spot. If we add the Running Total column to the right of this, we can see the values:

However, if we use the -30 there instead of 200, look what our Running Total is:

-50 is a lot closer to 0 than 180 is!

So, is there a mistake in the example you posted, or a mistake in the logic you posted/explained?

We need to completely and clearly understand the logic of how this is supposed to work if we want to try to come up with a solution.