Split values among relevant columns field

gentle_20052006 · Aug 5th 2022

Hi all,

I need the required qty based on the line qty and gross qty.

The Required qty is the difference of total line qty and gross qty. I need it line wise that's where I am not able to apply formula.

Column "D" is required for Formula or VBA code.

Pls help me to resolve the attached problem.

Thanks

**KjBox** · Aug 5th 2022

What do you mean by

Quote from gentle_20052006

I need it line wise

Are the values in Column D the required result? If so, then what is the logic to get those results?

**KjBox** · Aug 5th 2022

OK I worked out the logic.

Try the attached file, click the button on the sheet.

Code assigned to the button:

Code

Sub RqdQty()
    Dim x, i&, ii&, iii&

    With Sheet1.Cells(1).CurrentRegion
        x = .Value
        For i = 2 To UBound(x, 1)
            ii = 0: iii = 0
            If x(i, 1) = x(i + 1, 1) Then
                x(i, 4) = "-": ii = x(i, 2)
                Do Until x(i, 1) <> x(i + 1, 1)
                    If x(i, 2) > x(i, 3) Then
                        x(i, 4) = x(i, 2) - x(i, 3)
                        ii = ii + x(i + 1, 2)
                        iii = iii + x(i, 4)
                    Else
                        x(i, 4) = "-": ii = ii + x(i + 1, 2)
                    End If
                    i = i + 1
                    If i >= UBound(x, 1) Then Exit Do
                Loop
                If ii > x(i, 3) Then
                    x(i, 4) = (ii - x(i, 3)) - iii
                Else
                    x(i, 4) = "-"
                End If
            ElseIf x(i, 2) > x(i, 3) Then
                x(i, 4) = x(i, 2) - x(i, 3)
            Else
                x(i, 4) = "-"
            End If
        Next
        .Value = x
    End With
    
End Sub

Display More

gentle_20052006 · Aug 5th 2022

Thanks for your quick response.

The each lines should be intact/line wise i.e if input contains 100 rows I need required value column "d" for all 100 rows.

The logic for Required colum "D" is

A. The Gross qty of A, b, c, d, e, f, and G are 30,77,98,34,180,10 and 22 respectively.

1. Gross qty "A" of 30 should meet from line qty of "A" from 12 & 23. In meeting so,12 will get "exhausted" first and column"d" goes blank, then goes to 23 and from 23, only 18 will get "exhausted" from it and the remaining 5 is placed in column"d" against the row containing line qty 23.

2. Gross qty "b" of 77 should meet from line qty of "b" from 45,30, & 2. In meeting so, 45 will get "exhausted" first, then 30 gets "exhausted" and 2 gets "exhausted" from line qty and there is no remaining column"d" against all rows containing "b".

3. Gross qty "G" of 22 should meet from line qty of "G" from 30 & 30. In meeting so, 22 will get "exhausted" first, the remaining 8 is placed in column"d" against the row containing line qty 30. since we have exhausted gross qty from ""B17", the next 30 in "B18" goes to column"d" without any deduction.

Hope I am able to explain the logic. Still looking for formula/VBA code.

Thanks

gentle_20052006 · Aug 5th 2022

Thank you KjBox. It worked WOW thank you

**KjBox** · Aug 5th 2022

You're welcome

**KjBox** · Aug 5th 2022

Just realised the code will fail if the Item in Column A is an Item with just a single row.

Change the code to what is shown below to overcome this.

Code

Sub RqdQty()
    Dim x, i&, ii&, iii&

    With Sheet1.Cells(1).CurrentRegion
        x = .Value
        For i = 2 To UBound(x, 1)
            ii = 0: iii = 0
            If i < UBound(x, 1) Then
                If x(i, 1) = x(i + 1, 1) Then
                    x(i, 4) = "-": ii = x(i, 2)
                    Do Until x(i, 1) <> x(i + 1, 1)
                        If x(i, 2) > x(i, 3) Then
                            x(i, 4) = x(i, 2) - x(i, 3)
                            ii = ii + x(i + 1, 2)
                            iii = iii + x(i, 4)
                        Else
                            x(i, 4) = "-": ii = ii + x(i + 1, 2)
                        End If
                        i = i + 1
                        If i >= UBound(x, 1) Then Exit Do
                    Loop
                    If ii > x(i, 3) Then
                        x(i, 4) = (ii - x(i, 3)) - iii
                    Else
                        x(i, 4) = "-"
                    End If
                ElseIf x(i, 2) > x(i, 3) Then
                    x(i, 4) = x(i, 2) - x(i, 3)
                Else
                    x(i, 4) = "-"
                End If
            ElseIf x(i, 2) > x(i, 3) Then
                 x(i, 4) = x(i, 2) - x(i, 3)
            Else
                x(i, 4) = "-"
            End If
        Next
        .Value = x
        .Columns(4).HorizontalAlignment = -4152
    End With
    
End Sub

Display More

gentle_20052006 · Aug 6th 2022

Thank you very much KjBox.

When I applied code in the actual data, the requirement not fulfilled. I failed to foresee the outcome.

Can I get my revised qty as followed in column" E" if possible?

Thanks

**KjBox** · Aug 6th 2022

So to get the Revised Quantity the Line Quantity of the last entry for each Item is reduced by the Required Quantity?

**KjBox** · Aug 6th 2022

Probably easiest to do it with a formula, put this in E2 and copy down:

=IF($D3="","",IF(ROW(A3)<3,IF(IF(D3<>"-",B3-$D3,B3)<0,0,IF(D3<>"-",B3-D3,B3)),IF(AND(A3<>A2,A3=A4),IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-$D3,B3)),IF(AND(A3=A2,A3=A4),IF(OR(E2=C2,E2=0),0,IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-D3,B3))),IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-D3,B3))))))

You will also need to make a small change to the VBA code.

Change With Sheet1.Cells(1).CurrentRegion to With Sheet1.Cells(1).CurrentRegion.Resize(, 4)

I am attaching the sample file with additional Items, Line Quantities and Gross Quantities so as to test for all possible scenarios. The Revised Line Quantity column has the above formula in it and the change to the code made, click the button as before.

gentle_20052006 · Aug 8th 2022

Thank you very much.

Help arrived at right moment. Mission completed.

**KjBox** · Aug 8th 2022

You're welcome

Split values among relevant columns field

Participate now!

Share