Complicated for split and calculation data based on QTY for two sheets

[Omar M]

Hello,
I would do my project by macro , not by POWER QUER or PIVOT TABLE.
SO I have data in SAA sheet in range A:F and there are data for SS sheet J:N and in STOCK sheet is A:E
so the result will be in SSA sheet J:O (I put the formula in column O how should be) and in columns L,N in SS sheet , in columns C,E in STOCK sheet.

in data in SSA sheet A: F I would split data based on QTY column D so will brings date,id,price from A,B,E to J,K,M as to QTY for column D and put in column L should split QTY based on QTY is existed in both sheets SS,STOCK after matching ID then will subtract QTY in column L,C and column N,E should equal UNIT PRICE *TOTAL. for SS,STOCK sheet .
EXAMPLE:
AVSD HJY00 ID = 28 in Saa sheet will take QTY from row 2 in STOCK sheet and from row2,4 in SS sheet ). so when split data will take QTY from old dates for STOCK,SS sheets in this case there are 4 ,22 QTY for old dates will be =26 and remaining 2 then will take QT from next row in SS sheet because STOCK sheet will be zero after subtract .
every QTY bring from both sheets STOCK,SS sheet.
when split data always should start from STOCK sheet and from old date after that brings from SS sheet if the QTY doesn't cove from SOCK sheet. when take whole QTY from old date from STOCK or SS sheet then should subtract from QTY and show zero
really important notice: when increase data in A:J in SAA sheet and split data then should ignore any ID contains zero for STOCK,SS sheets and move next row for old date.
as you you see there are duplicates ID in SAA sheet, each duplicate ID should split alone depends on how QTY should cover for both sheets.
always should search for QTY cover for STOCK sheet first if it's not enough should complete the QTY for old dates from SS sheet.
will not be duplicates in STOCK sheet.

every time should delete data in SSA sheet J:O before brings report.
and when add new data in SSA sheet in A:F then will ignores QTY=0 for each ID in both sheets(SS,STOCK)

1om.xlsm
	J	K	L	M	N
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	22.00	222.00	4,884.00
3	02/03/2024	AZSDC	10.00	120.00	1,200.00
4	03/03/2024	AVSD HJY00	10.00	223.00	2,230.00
5	03/03/2024	ASCV TYU T566	10.00	100.00	1,000.00
6	05/03/2024	AZSDC	5.00	123.00	615.00
7	06/03/2024	ASCV TYU T566	5.00	110.00	550.00
8	07/03/2024	AZSDC	2.00	124.00	248.00
9	07/03/2024	BBSDFRT-00	1.00	120.00	120.00
10	08/03/2024	BBSDFRT-01	2.00	124.00	248.00
11	10/03/2024	VBGHYT 34YTY 66	2.00	220.00	440.00
12	12/03/2024	VBGHYT 34YTY 66	2.00	225.00	450.00
SS

1om.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	ID	NAME	QTY	PRICE	TOTAL
2	11/03/2024	AVSD HJY00	amren	28.00	250.00	7,000.00
3	12/03/2024	AZSDC	OMRAN	5.00	130.00	650.00
4	13/03/2024	AVSD HJY00	amuri	3.00	260.00	780.00
5	14/03/2024	AZSDC	OMRAN	6.00	140.00	840.00
6	16/03/2024	ASCV TYU T566	AMERN	15.00	120.00	1,800.00
7
8
9
10
saa

Cell Formulas
Range	Formula
F2:F6	F2	=D2*E2

1om.xlsm
	A	B	C	D	E
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	4.00	228.00	912.00
3	02/03/2024	AVSD HJY01	10.00	120.00	1,200.00
4	03/03/2024	AVSD HJY02	10.00	90.00	900.00
5	05/03/2024	AZSDC	4.00	123.00	492.00
6	07/03/2024	BBSDFRT-00	1.00	120.00	120.00
7	08/03/2024	BBSDFRT-01	2.00	124.00	248.00
8	10/03/2024	BBSDFRT-02	2.00	220.00	440.00
9	12/03/2024	BBSDFRT-03	2.00	225.00	450.00
stock

result in ssa sheet

1om.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	ID	NAME	QTY	PRICE	TOTAL
2	11/03/2024	AVSD HJY00	amren	28.00	250.00	7,000.00
3	12/03/2024	AZSDC	OMRAN	5.00	130.00	650.00
4	13/03/2024	AVSD HJY00	amuri	3.00	260.00	780.00				DATE	ID	QTY	SS PRICE	CC PRICE	TOTAL
5	14/03/2024	AZSDC	OMRAN	6.00	140.00	840.00				11/03/2024	AVSD HJY00	4	250.00	228.00	88.00
6	16/03/2024	ASCV TYU T566	AMERN	15.00	120.00	1,800.00				11/03/2024	AVSD HJY00	22.00	250.00	222.00	616.00
7										11/03/2024	AVSD HJY00	2.00	250.00	223.00	54.00
8										12/03/2024	AZSDC	4.00	130.00	123.00	28.00
9										12/03/2024	AZSDC	1.00	130.00	120.00	10.00
10										13/03/2024	AVSD HJY00	3.00	260.00	223.00	111.00
11										14/03/2024	AZSDC	6.00	140.00	120.00	120.00
12										16/03/2024	ASCV TYU T566	10.00	120.00	100.00	200.00
13										16/03/2024	ASCV TYU T566	5.00	120.00	110.00	50.00
saa

Cell Formulas
Range	Formula
F2:F6	F2	=D2*E2
O5:O13	O5	=(M5-N5)*L5

and result in
column L,C and column N,E for SS,STOCK sheet .

1om.xlsm
	J	K	L	M	N
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	0.00	222.00	0.00
3	02/03/2024	AZSDC	3.00	120.00	1,200.00
4	03/03/2024	AVSD HJY00	5.00	223.00	2,230.00
5	03/03/2024	ASCV TYU T566	0.00	100.00	0.00
6	05/03/2024	AZSDC	5.00	123.00	615.00
7	06/03/2024	ASCV TYU T566	0.00	110.00	0.00
8	07/03/2024	AZSDC	2.00	124.00	248.00
9	07/03/2024	BBSDFRT-00	1.00	120.00	120.00
10	08/03/2024	BBSDFRT-01	2.00	124.00	248.00
11	10/03/2024	VBGHYT 34YTY 66	2.00	220.00	440.00
12	12/03/2024	VBGHYT 34YTY 66	2.00	225.00	450.00
SS

1om.xlsm
	A	B	C	D	E
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	0.00	228.00	0.00
3	02/03/2024	AVSD HJY01	10.00	120.00	1,200.00
4	03/03/2024	AVSD HJY02	10.00	90.00	900.00
5	05/03/2024	AZSDC	0.00	123.00	0.00
6	07/03/2024	BBSDFRT-00	1.00	120.00	120.00
7	08/03/2024	BBSDFRT-01	2.00	124.00	248.00
8	10/03/2024	BBSDFRT-02	2.00	220.00	440.00
9	12/03/2024	BBSDFRT-03	2.00	225.00	450.00
stock

Cell Formulas
Range	Formula
E2:E9	E2	=C2*D2

I know this is really complicated . if my way is too hard and could make easy by another option I will accept but the most important by macro , not else.
if you need more example for some cases if this is not clear just tell me and I will do that soon .
thanks for all of experts read my thread and I hope to not read and leave without any help if you could .

 

[Omar M]

I hope read my last comment.

 

[Sam_D_Ben]

Hey, How are you, I'll check it.

 

[Omar M]

fine , I hope you're too.

'll check it.

thanks for confirmation .

 

[Sam_D_Ben]

Hi, Please ignore the past modules. Also sorry to say you are going away from the orginal post by adding more conditions Please make sure your SAA, Stock and SS and SAA Output Range as it is.
Please use the detailed output sheet with the date& time stamp for any error rectification. Thank You

Sub ProcessSAA()
    Dim wsSAA As Worksheet, wsStock As Worksheet, wsSS As Worksheet, wsCons As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim i As Long, j As Long, k As Long
    Dim productID As Variant, dateSAA As Variant
    Dim reqQty As Double, remainingQty As Double
    Dim candidateQty As Double, allocateQty As Double
    Dim candidateCostPrice As Double
    Dim candidateDate As Variant
    Dim priceSAA As Double
    Dim outRowSAA As Long, outRowCons As Long
    Dim consSheetName As String
    
    Set wsSAA = ThisWorkbook.Sheets("SAA")
    Set wsStock = ThisWorkbook.Sheets("Stock")
    Set wsSS = ThisWorkbook.Sheets("SS")
    
    consSheetName = Format(Now, "ddmmm_hhmm")
    Set wsCons = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsCons.Name = consSheetName
    
    With wsCons
        .Range("A1").Value = "SAA Date"
        .Range("B1").Value = "Product ID"
        .Range("C1").Value = "Req Qty"
        .Range("D1").Value = "Alloc Qty"
        .Range("E1").Value = "SAA Price"
        .Range("F1").Value = "CC Price"
        .Range("G1").Value = "Source Date"
        .Range("H1").Value = "Calculated Total"
        .Range("I1").Value = "Source"
    End With
    outRowCons = 2
    
    wsSAA.Range("J2:P" & wsSAA.Rows.Count).ClearContents
    
    With wsSAA
        .Range("J1").Value = "Date"
        .Range("K1").Value = "Product ID"
        .Range("L1").Value = "Quantity"
        .Range("M1").Value = "Price"
        .Range("N1").Value = "Product CC Price"
        .Range("O1").Value = "Total"
        .Range("P1").Value = "Source"
    End With
    outRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "J").End(xlUp).Row + 1
    
    lastRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRowSAA
        If Trim(wsSAA.Cells(i, "G").Value) = "" Then
            dateSAA = wsSAA.Cells(i, "A").Value
            productID = wsSAA.Cells(i, "B").Value
            reqQty = wsSAA.Cells(i, "D").Value
            priceSAA = wsSAA.Cells(i, "E").Value
            remainingQty = reqQty
            
            lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
            For j = 2 To lastRowStock
                If wsStock.Cells(j, "B").Value = productID Then
                    If wsStock.Cells(j, "A").Value <= dateSAA Then
                        candidateQty = wsStock.Cells(j, "C").Value
                        If candidateQty > 0 And remainingQty > 0 Then
                            allocateQty = Application.WorksheetFunction.Min(remainingQty, candidateQty)
                            candidateCostPrice = wsStock.Cells(j, "D").Value
                            candidateDate = wsStock.Cells(j, "A").Value
                            
                            
                            With wsSAA
                                .Cells(outRowSAA, "J").Value = dateSAA
                                .Cells(outRowSAA, "K").Value = productID
                                .Cells(outRowSAA, "L").Value = allocateQty
                                .Cells(outRowSAA, "M").Value = priceSAA
                                .Cells(outRowSAA, "N").Value = candidateCostPrice
                                .Cells(outRowSAA, "O").Value = (priceSAA - candidateCostPrice) * allocateQty
                                .Cells(outRowSAA, "P").Value = "Stock"
                            End With
                            outRowSAA = outRowSAA + 1
                            
                            With wsCons
                                .Cells(outRowCons, "A").Value = dateSAA
                                .Cells(outRowCons, "B").Value = productID
                                .Cells(outRowCons, "C").Value = reqQty
                                .Cells(outRowCons, "D").Value = allocateQty
                                .Cells(outRowCons, "E").Value = priceSAA
                                .Cells(outRowCons, "F").Value = candidateCostPrice
                                .Cells(outRowCons, "G").Value = candidateDate
                                .Cells(outRowCons, "H").Value = (priceSAA - candidateCostPrice) * allocateQty
                                .Cells(outRowCons, "I").Value = "Stock"
                            End With
                            outRowCons = outRowCons + 1
                            
                            wsStock.Cells(j, "C").Value = candidateQty - allocateQty
                            wsStock.Cells(j, "E").Value = wsStock.Cells(j, "C").Value * candidateCostPrice
                            If wsStock.Cells(j, "C").Value = 0 Then
                                wsStock.Cells(j, "F").Value = "Processed"
                            End If
                            
                            remainingQty = remainingQty - allocateQty
                            If remainingQty <= 0 Then Exit For
                        End If
                    End If
                End If
            Next j
            
            If remainingQty > 0 Then
                lastRowSS = wsSS.Cells(wsSS.Rows.Count, "J").End(xlUp).Row
                For k = 2 To lastRowSS
                    If wsSS.Cells(k, "K").Value = productID Then
                        If wsSS.Cells(k, "J").Value <= dateSAA Then
                            candidateQty = wsSS.Cells(k, "L").Value
                            If candidateQty > 0 And remainingQty > 0 Then
                                allocateQty = Application.WorksheetFunction.Min(remainingQty, candidateQty)
                                candidateCostPrice = wsSS.Cells(k, "M").Value
                                candidateDate = wsSS.Cells(k, "J").Value
                                
                                With wsSAA
                                    .Cells(outRowSAA, "J").Value = dateSAA
                                    .Cells(outRowSAA, "K").Value = productID
                                    .Cells(outRowSAA, "L").Value = allocateQty
                                    .Cells(outRowSAA, "M").Value = priceSAA
                                    .Cells(outRowSAA, "N").Value = candidateCostPrice
                                    .Cells(outRowSAA, "O").Value = (priceSAA - candidateCostPrice) * allocateQty
                                    .Cells(outRowSAA, "P").Value = "SS"
                                End With
                                outRowSAA = outRowSAA + 1
                                
                                With wsCons
                                    .Cells(outRowCons, "A").Value = dateSAA
                                    .Cells(outRowCons, "B").Value = productID
                                    .Cells(outRowCons, "C").Value = reqQty
                                    .Cells(outRowCons, "D").Value = allocateQty
                                    .Cells(outRowCons, "E").Value = priceSAA
                                    .Cells(outRowCons, "F").Value = candidateCostPrice
                                    .Cells(outRowCons, "G").Value = candidateDate
                                    .Cells(outRowCons, "H").Value = (priceSAA - candidateCostPrice) * allocateQty
                                    .Cells(outRowCons, "I").Value = "SS"
                                End With
                                outRowCons = outRowCons + 1
                                
                                wsSS.Cells(k, "L").Value = candidateQty - allocateQty
                                wsSS.Cells(k, "N").Value = wsSS.Cells(k, "L").Value * candidateCostPrice
                                If wsSS.Cells(k, "L").Value = 0 Then
                                    wsSS.Cells(k, "O").Value = "processed"
                                End If
                                
                                remainingQty = remainingQty - allocateQty
                                If remainingQty <= 0 Then Exit For
                            End If
                        End If
                    End If
                Next k
            End If
            
            wsSAA.Cells(i, "G").Value = "Processed"
        End If
    Next i
    
    MsgBox "Processing complete." & vbCrLf & "Consolidated output saved in sheet: " & wsCons.Name
End Sub

 

[Omar M]

Hi again,
can you guide me how works new version?
I see add new sheet every time then will file contains too much sheets in workbook!
and when run every time will clear data in columns J:O without keep old data!

 

[Sam_D_Ben]

I see add new sheet every time then will file contains too much sheets in workbook!
and when run every time will clear data in columns J:O without keep old data!

Please try this,

Sub ProcessSAA()
    
    Dim wsSAA As Worksheet, wsStock As Worksheet, wsSS As Worksheet, wsCons As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim i As Long, j As Long, k As Long
    Dim ProductID As Variant, dateSAA As Variant
    Dim reqQty As Double, remainingQty As Double
    Dim invQty As Double, allocateQty As Double, qtyLeft As Double
    Dim invCostPrice As Double
    Dim invDate As Variant
    Dim priceSAA As Double
    Dim outRowSAA As Long, outRowCons As Long
    Dim consSheetName As String, uniformFormat As String
    
    uniformFormat = "yyyy-mm-dd"
        
    Set wsSAA = ThisWorkbook.Sheets("SAA")
    Set wsStock = ThisWorkbook.Sheets("Stock")
    Set wsSS = ThisWorkbook.Sheets("SS")
    
    consSheetName = Format(Now, "mmmdd_hhmmss")
    Set wsCons = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsCons.Name = consSheetName
    
    With wsCons
        .Range("A1").Value = "SAA Date"
        .Range("B1").Value = "Product ID"
        .Range("C1").Value = "Req Qty"
        .Range("D1").Value = "Alloc Qty"
        .Range("E1").Value = "Qty Left"
        .Range("F1").Value = "SAA_Price"
        .Range("G1").Value = "CC_Price"
        .Range("H1").Value = "Source Date"
        .Range("I1").Value = "Total"
        .Range("J1").Value = "Source"
    End With
    outRowCons = 2
    
    With wsSAA
        If .Range("J1").Value = "" Then
            .Range("J1").Value = "Date"
            .Range("K1").Value = "Product ID"
            .Range("L1").Value = "Alloc Qty"
            .Range("M1").Value = "Qty Left"
            .Range("N1").Value = "SAA_Price"
            .Range("O1").Value = "CC_Price"
            .Range("P1").Value = "Total"
            .Range("Q1").Value = "Source"
        End If
    End With
    outRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "J").End(xlUp).Row + 1
    
    lastRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRowSAA
        If Trim(wsSAA.Cells(i, "G").Value) = "" Then
            dateSAA = wsSAA.Cells(i, "A").Value
            ProductID = wsSAA.Cells(i, "B").Value
            reqQty = wsSAA.Cells(i, "D").Value
            priceSAA = wsSAA.Cells(i, "E").Value
            remainingQty = reqQty
            
            lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
            For j = 2 To lastRowStock
                If wsStock.Cells(j, "B").Value = ProductID Then
                    If wsStock.Cells(j, "A").Value <= dateSAA Then
                        invQty = wsStock.Cells(j, "C").Value
                        If invQty > 0 And remainingQty > 0 Then
                            allocateQty = Application.WorksheetFunction.Min(remainingQty, invQty)
                            invCostPrice = wsStock.Cells(j, "D").Value
                            invDate = wsStock.Cells(j, "A").Value
                            qtyLeft = invQty - allocateQty
                            
                            With wsSAA
                                .Cells(outRowSAA, "J").Value = Format(dateSAA, uniformFormat)
                                .Cells(outRowSAA, "K").Value = ProductID
                                .Cells(outRowSAA, "L").Value = allocateQty
                                .Cells(outRowSAA, "M").Value = qtyLeft
                                .Cells(outRowSAA, "N").Value = priceSAA
                                .Cells(outRowSAA, "O").Value = invCostPrice
                                .Cells(outRowSAA, "P").Value = (priceSAA - invCostPrice) * allocateQty
                                .Cells(outRowSAA, "Q").Value = "Stock"
                            End With
                            outRowSAA = outRowSAA + 1
                            
                            With wsCons
                                .Cells(outRowCons, "A").Value = Format(dateSAA, uniformFormat)
                                .Cells(outRowCons, "B").Value = ProductID
                                .Cells(outRowCons, "C").Value = reqQty
                                .Cells(outRowCons, "D").Value = allocateQty
                                .Cells(outRowCons, "E").Value = qtyLeft
                                .Cells(outRowCons, "F").Value = priceSAA
                                .Cells(outRowCons, "G").Value = invCostPrice
                                .Cells(outRowCons, "H").Value = Format(invDate, uniformFormat)
                                .Cells(outRowCons, "I").Value = (priceSAA - invCostPrice) * allocateQty
                                .Cells(outRowCons, "J").Value = "Stock"
                            End With
                            outRowCons = outRowCons + 1
                            
                            wsStock.Cells(j, "C").Value = qtyLeft
                            wsStock.Cells(j, "E").Value = qtyLeft * invCostPrice
                            If wsStock.Cells(j, "C").Value = 0 Then wsStock.Cells(j, "F").Value = "Processed"
                            
                            remainingQty = remainingQty - allocateQty
                            If remainingQty <= 0 Then Exit For
                        End If
                    End If
                End If
            Next j
            
            If remainingQty > 0 Then
                lastRowSS = wsSS.Cells(wsSS.Rows.Count, "J").End(xlUp).Row
                For k = 2 To lastRowSS
                    If wsSS.Cells(k, "K").Value = ProductID Then
                        If wsSS.Cells(k, "J").Value <= dateSAA Then
                            invQty = wsSS.Cells(k, "L").Value
                            If invQty > 0 And remainingQty > 0 Then
                                allocateQty = Application.WorksheetFunction.Min(remainingQty, invQty)
                                invCostPrice = wsSS.Cells(k, "M").Value
                                invDate = wsSS.Cells(k, "J").Value
                                qtyLeft = invQty - allocateQty
                                
                                With wsSAA
                                    .Cells(outRowSAA, "J").Value = Format(dateSAA, uniformFormat)
                                    .Cells(outRowSAA, "K").Value = ProductID
                                    .Cells(outRowSAA, "L").Value = allocateQty
                                    .Cells(outRowSAA, "M").Value = qtyLeft
                                    .Cells(outRowSAA, "N").Value = priceSAA
                                    .Cells(outRowSAA, "O").Value = invCostPrice
                                    .Cells(outRowSAA, "P").Value = (priceSAA - invCostPrice) * allocateQty
                                    .Cells(outRowSAA, "Q").Value = "SS"
                                End With
                                outRowSAA = outRowSAA + 1
                                
                                With wsCons
                                    .Cells(outRowCons, "A").Value = Format(dateSAA, uniformFormat)
                                    .Cells(outRowCons, "B").Value = ProductID
                                    .Cells(outRowCons, "C").Value = reqQty
                                    .Cells(outRowCons, "D").Value = allocateQty
                                    .Cells(outRowCons, "E").Value = qtyLeft
                                    .Cells(outRowCons, "F").Value = priceSAA
                                    .Cells(outRowCons, "G").Value = invCostPrice
                                    .Cells(outRowCons, "H").Value = Format(invDate, uniformFormat)
                                    .Cells(outRowCons, "I").Value = (priceSAA - invCostPrice) * allocateQty
                                    .Cells(outRowCons, "J").Value = "SS"
                                End With
                                outRowCons = outRowCons + 1
                                
                                wsSS.Cells(k, "L").Value = qtyLeft
                                wsSS.Cells(k, "N").Value = qtyLeft * invCostPrice
                                If wsSS.Cells(k, "L").Value = 0 Then wsSS.Cells(k, "O").Value = "Processed"
                                
                                remainingQty = remainingQty - allocateQty
                                If remainingQty <= 0 Then Exit For
                            End If
                        End If
                    End If
                Next k
            End If
            
            wsSAA.Cells(i, "G").Value = "Processed"
        End If
    Next i
    
    MsgBox "SAA Processing complete."
    End Sub

 

[Omar M]

this is what I got

1om.xlsm
	J	K	L	M	N	O	P	Q
1	DATE	ID	QTY	SS PRICE	CC PRICE	TOTAL
2	11/03/2024	AVSD HJY00	4.00	0.00	250.00	228.00	88	Stock
3	11/03/2024	AVSD HJY00	22.00	0.00	250.00	222.00	616	SS
4	11/03/2024	AVSD HJY00	2.00	8.00	250.00	223.00	54	SS
5	12/03/2024	AZSDC	5.00	5.00	130.00	120.00	50	SS
6	13/03/2024	AVSD HJY00	3.00	5.00	260.00	223.00	111	SS
7	14/03/2024	AZSDC	5.00	0.00	140.00	120.00	100	SS
8	14/03/2024	AZSDC	1.00	4.00	140.00	123.00	17	SS
9	16/03/2024	ASCV TYU T566	1.00	9.00	120.00	100.00	20	SS
saa

may you see column M and what are values in columns P,Q?
there are errors the prices in column M !

 

[Sam_D_Ben]

may you see column M and what are values in columns P,Q?
there are errors the prices in column M !

That is not errors,
Colm L is the Allocated Quantity from Stock or SS
M is the Quantity Left in Stock or SS after allocation
Source (Q) is to know from where allocation happened

Stock Management.xlsm
	J	K	L	M	N	O	P	Q
1	Date	Product ID	Alloc Qty	Qty Left	SAA_Price	CC_Price	Total	Source
2	3/11/2024	AVSD HJY00	4	0	250	228	88	Stock
3	3/11/2024	AVSD HJY00	22	0	250	222	616	SS
4	3/11/2024	AVSD HJY00	2	8	250	223	54	SS
5	3/12/2024	AZSDC	4	0	130	123	28	Stock
6	3/12/2024	AZSDC	1	9	130	120	10	SS
7	3/13/2024	AVSD HJY00	3	5	260	223	111	SS
8	3/14/2024	AZSDC	6	3	140	120	120	SS
9	3/14/2024	ASCV TYU T566	10	0	120	100	200	SS
10	3/14/2024	ASCV TYU T566	5	0	120	110	50	SS
SAA

 

[Omar M]

thanks for clarification.
I will test again and see how goes.

 

[Omar M]

when add new data in A:G for saa sheet will not split in J:Q .