Hi,

I am looking for VBA code to solve a current problem.

I have a list of numerical (row) values (Column A) where I am sorting the column (by VBA code) in descending order. Column B is the Bin location. These rows are then output to another worksheet (Column 1 shows the amount and Column 2 displays the Bin location and the amount to be shipped from each Bin).

Firstly, I need to evaluate each cell value against a specified threshold amount (in this example it is 27)

If Data Sheet
Column A = 27
Column B = Bin1
Then
27 Bin1;

Then the Output Sheet should be
Column 1 = 27
Column 2 = Bin1 27;
written as
27 Bin1 27;

NOTE: After each output, the original (A & B) columns are deleted where output and then the remainder are re-sorted.

I can resolve issues where the Cell value is greater than the Threshold and where the Cell value is equal to the Threshold. However, I am stuck where the Cell value is less than the Threshold.

If the 1st Cell value is less than the specified amount (remember they have been sorted), then I need to calculate each row and determine whether multiple values are equal to the specified amount and output the combined amount.

For example:
26 Bin2
24 Bin3
4 Bin8
2 Bin9
2 Bin10
1 Bin11
1 Bin12

Where (1st line)
27 Bin2 26; Bin11 1;
is written to the output file

The column is again sorted in descending order and then the column list becomes:
24 Bin3
4 Bin8
2 Bin9
2 Bin10
1 Bin12

And then (2nd line)
27 Bin3 24, Bin9 2; Bin12 1;
is then written to the Output sheet

Leaving on the Original sheet
4 Bin8
2 Bin10

Which is finally output as (3rd line)
6 Bin8 4; Bin10 2;

The complete output for section 3 should look like this:
27 Bin2 26; Bin11 1;
27 Bin3 24, Bin9 2; Bin12 1;
6 Bin8 4; Bin10 2;

In this example what i need to do is establish if there are ANY combinations that are equal 27 if so, display those first and then output each additional item/items that are less than 27.

so as in my last example:

27 (+ the relevent bin numbers and their associated values)
27 (+ the relevent bin numbers and their associated values)
6 (+ the relevent bin numbers and their associated values)

For course there may be instances where there aren't any combinations equal to 27, therefore they could all be output as separate lines. Or as in the following example, the 2nd and 3rd lines may equal 27 but not the 1st line.

So,
21 Bin1
12 Bin2
15 Bin3

is Output as:
27 Bin2 12; Bin3 15;
21 Bin 1 21;

Hope this makes sense.

Thanks for any help,


Neal