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 put both codes in the same module(module1).

 

[Sam_D_Ben]

I put both codes in the same module(module1).

I am sorry, I was at work, that is two different modules. Put the first code in Module1 and the second code in Module2. Run Module1 first and then run module2 and see how it goes.

 

[Omar M]

Put the first code in Module1 and the second code in Module2. Run Module1 first and then run module2 and see how it goes.

yes it works when run for each code alone .
and I hope adjusting my requirements

because should copy to bottom without repeat dividing again for data have already divided to fix this problem should add word COMPLETE in column G for each ID have already divided and ignore them from splitting when try splitting again when every time run the macro.
so should populate complete word for each ID in column B and when add new data without COMPLETE word is existed then will ignore any ID contains COMPLETE word from dividing.

sorry about it.

 

[Sam_D_Ben]

yes it works when run for each code alone .
and I hope adjusting my requirements

sorry about it.

No problem, I'll check.

 

[Sam_D_Ben]

I hope adjusting my requirements

Updated, As i said earlier' Please put both in two modules and see how it work.
Module1

Sub SplitDataAndDeductFromAndUpdateJ2M()
    Dim wsSSA As Worksheet, wsSS As Worksheet, wsStock As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim i As Long, j As Long, k As Long
    Dim SAA_ID As String, SAA_Date As Variant
    Dim SAA_Qty As Double
    Dim remQty As Double, allocQty As Double, availQty As Double
    Dim outRow As Long
    Dim unitPrice As Double, ssPrice As Double
    Dim matchID As String, price As Double
    Dim found As Boolean
    Dim lastRowSAA_K As Long
    Dim matchDate As Variant
    
    Set wsSSA = ThisWorkbook.Sheets("SAA")
    Set wsSS = ThisWorkbook.Sheets("SS")
    Set wsStock = ThisWorkbook.Sheets("STOCK")
    
    If IsEmpty(wsSSA.Cells(1, "G").Value) Then
        wsSSA.Cells(1, "G").Value = "NOTICE"
    End If
    
    lastRowSAA = wsSSA.Cells(wsSSA.Rows.Count, "A").End(xlUp).Row
    lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
    lastRowSS = wsSS.Cells(wsSS.Rows.Count, "J").End(xlUp).Row
    
    outRow = wsSSA.Cells(wsSSA.Rows.Count, "J").End(xlUp).Row
    If outRow < 2 Then
        outRow = 2
    Else
        outRow = outRow + 1
    End If
    
    For i = 2 To lastRowSAA
        If Trim(wsSSA.Cells(i, "G").Value) = "Complete" Then GoTo NextRow
        
        SAA_Date = wsSSA.Cells(i, "A").Value
        SAA_ID = Trim(wsSSA.Cells(i, "B").Value)
        SAA_Qty = wsSSA.Cells(i, "D").Value
        
        If SAA_Qty <= 0 Then GoTo NextRow
        remQty = SAA_Qty
        
        For j = 2 To lastRowStock
            If Trim(wsStock.Cells(j, "B").Value) = SAA_ID And _
               wsStock.Cells(j, "C").Value > 0 And _
               wsStock.Cells(j, "A").Value < SAA_Date Then
                availQty = wsStock.Cells(j, "C").Value
                allocQty = IIf(availQty >= remQty, remQty, availQty)
                
                wsStock.Cells(j, "C").Value = availQty - allocQty
                remQty = remQty - allocQty
                
                unitPrice = wsStock.Cells(j, "E").Value
                
                wsSSA.Cells(outRow, "J").Value = SAA_Date
                wsSSA.Cells(outRow, "K").Value = SAA_ID
                wsSSA.Cells(outRow, "L").Value = allocQty
                outRow = outRow + 1
                
                If remQty <= 0 Then Exit For
            End If
        Next j
        
        If remQty > 0 Then
            For k = 2 To lastRowSS
                If Trim(wsSS.Cells(k, "K").Value) = SAA_ID And _
                   wsSS.Cells(k, "L").Value > 0 And _
                   wsSS.Cells(k, "J").Value < SAA_Date Then
                   
                    availQty = wsSS.Cells(k, "L").Value
                    allocQty = IIf(availQty >= remQty, remQty, availQty)
                    
                    wsSS.Cells(k, "L").Value = availQty - allocQty
                    remQty = remQty - allocQty
                    
                    ssPrice = wsSS.Cells(k, "N").Value
                    
                    wsSSA.Cells(outRow, "J").Value = SAA_Date
                    wsSSA.Cells(outRow, "K").Value = SAA_ID
                    wsSSA.Cells(outRow, "L").Value = allocQty
                    wsSSA.Cells(outRow, "M").Value = ssPrice
                    outRow = outRow + 1
                    
                    If remQty <= 0 Then Exit For
                End If
            Next k
        End If
        
NextRow:
        wsSSA.Cells(i, "G").Value = "Complete"
    Next i
    
    lastRowSAA_K = wsSSA.Cells(wsSSA.Rows.Count, "K").End(xlUp).Row
    
    For i = 2 To lastRowSAA_K
        matchID = Trim(wsSSA.Cells(i, "K").Value)
        matchDate = wsSSA.Cells(i, "J").Value
        found = False
        
        For j = 2 To lastRowSAA
            If Trim(wsSSA.Cells(j, "B").Value) = matchID And wsSSA.Cells(j, "A").Value = matchDate Then
                price = wsSSA.Cells(j, "E").Value
                wsSSA.Cells(i, "M").Value = price
                found = True
                Exit For
            End If
        Next j
        
        If Not found Then
            wsSSA.Cells(i, "M").Value = "Price not found"
        End If
    Next i
    
    wsSSA.Range("J2:J" & lastRowSAA_K).NumberFormat = "dd/mm/yyyy"
    
    MsgBox "Processing complete!"
End Sub

Module2

Option Explicit

Sub UpdateSAA_ColumnsNAndO()
    Call UpdateSAA_ColumnCCPrice
    Call UpdateSAA_ColumnO
End Sub

Sub UpdateSAA_ColumnCCPrice()
    Dim wsSSA As Worksheet, wsStock As Worksheet, wsSS As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim dictPrices As Object
    Dim i As Long, j As Long
    Dim SAA_ID As String
    Dim priceList As Collection
    Dim priceIndex As Integer
    Dim k As Long
    Dim foundPrice As Boolean
    Dim m As Long
    Dim matched As Boolean
    
    Set wsSSA = ThisWorkbook.Sheets("SAA")
    Set wsStock = ThisWorkbook.Sheets("STOCK")
    Set wsSS = ThisWorkbook.Sheets("SS")
    
    lastRowSAA = wsSSA.Cells(wsSSA.Rows.Count, "K").End(xlUp).Row
    lastRowStock = wsStock.Cells(wsStock.Rows.Count, "B").End(xlUp).Row
    lastRowSS = wsSS.Cells(wsSS.Rows.Count, "K").End(xlUp).Row
    
    Set dictPrices = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRowStock
        SAA_ID = Trim(wsStock.Cells(i, "B").Value)
        If SAA_ID <> "" Then
            If Not dictPrices.Exists(SAA_ID) Then
                Set priceList = New Collection
                dictPrices.Add SAA_ID, priceList
            Else
                Set priceList = dictPrices(SAA_ID)
            End If
            priceList.Add wsStock.Cells(i, "D").Value
        End If
    Next i
    
    For i = 2 To lastRowSS
        SAA_ID = Trim(wsSS.Cells(i, "K").Value)
        If SAA_ID <> "" Then
            If Not dictPrices.Exists(SAA_ID) Then
                Set priceList = New Collection
                dictPrices.Add SAA_ID, priceList
            Else
                Set priceList = dictPrices(SAA_ID)
            End If
            priceList.Add wsSS.Cells(i, "M").Value
        End If
    Next i
    
    For i = 2 To lastRowSAA
        SAA_ID = Trim(wsSSA.Cells(i, "K").Value)
        
        If dictPrices.Exists(SAA_ID) Then
            Set priceList = dictPrices(SAA_ID)
            priceIndex = Application.CountIf(wsSSA.Range("K2:K" & i), SAA_ID) - 1
            
            If priceIndex <= priceList.Count - 1 Then
                wsSSA.Cells(i, "N").Value = priceList.Item(priceIndex + 1)
            Else
                foundPrice = False
                For k = i - 1 To 2 Step -1
                    If Trim(wsSSA.Cells(k, "K").Value) = SAA_ID Then
                        wsSSA.Cells(i, "N").Value = wsSSA.Cells(k, "N").Value
                        foundPrice = True
                        Exit For
                    End If
                Next k
                If Not foundPrice Then
                    wsSSA.Cells(i, "N").Value = priceList.Item(priceList.Count)
                End If
            End If
        Else
            matched = False
            For m = i - 1 To 2 Step -1
                If Trim(wsSSA.Cells(m, "K").Value) = SAA_ID Then
                    wsSSA.Cells(i, "N").Value = wsSSA.Cells(m, "N").Value
                    matched = True
                    Exit For
                End If
            Next m
            If Not matched Then
                wsSSA.Cells(i, "N").Value = ""
            End If
        End If
    Next i
    
    MsgBox "Column N updated successfully!"
End Sub


Sub UpdateSAA_ColumnO()
    Dim wsSSA As Worksheet
    Dim lastRowSAA As Long
    Dim i As Long
    Dim result As Double
    
    Set wsSSA = ThisWorkbook.Sheets("SAA")
    
    lastRowSAA = wsSSA.Cells(wsSSA.Rows.Count, "N").End(xlUp).Row
    
    For i = 2 To lastRowSAA
        If Not IsEmpty(wsSSA.Cells(i, "M").Value) And _
           Not IsEmpty(wsSSA.Cells(i, "N").Value) And _
           Not IsEmpty(wsSSA.Cells(i, "L").Value) Then
           
            result = (wsSSA.Cells(i, "M").Value - wsSSA.Cells(i, "N").Value) * wsSSA.Cells(i, "L").Value
            wsSSA.Cells(i, "O").Value = result
        End If
    Next i
End Sub

Thank You

 

[Omar M]

thanks again
I have two things:
first doesn't show values in TOTAL column when run two codes .

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

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

and when add new data in SS sheet

1om.xlsm
	J	K	L	M	N
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	1.00	222.00	4,884.00
3	02/03/2024	AZSDC	3.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	0.00	100.00	1,000.00
6	05/03/2024	AZSDC	5.00	123.00	615.00
7	06/03/2024	ASCV TYU T566	0.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
13	13/03/2024	ASCV TYU T566	2.00	223	446
14	14/03/2024	ASCV TYU T566	7.00	225.00	1,575.00
SS

and add new in SAA 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	NOTICE			DATE	ID	QTY	SS PRICE	CC PRICE	TOTAL
2	11/03/2024	AVSD HJY00	amren	28.00	250.00	7,000.00	Complete			11/03/2024	AVSD HJY00	4.00	250.00	228.00
3	12/03/2024	AZSDC	OMRAN	5.00	130.00	650.00	Complete			11/03/2024	AVSD HJY00	24.00	250.00	222.00
4	13/03/2024	AVSD HJY00	amuri	3.00	260.00	780.00	Complete			12/03/2024	AZSDC	4.00	130.00	123.00
5	14/03/2024	AZSDC	OMRAN	6.00	140.00	840.00	Complete			12/03/2024	AZSDC	1.00	130.00	120.00
6	16/03/2024	ASCV TYU T566	AMERN	1.00	120.00	120.00	Complete			13/03/2024	AVSD HJY00	3.00	260.00	223.00
7	17/03/2024	ASCV TYU T566	AMERN	2.00	120.00	240.00				14/03/2024	AZSDC	6.00	140.00	123.00
8	18/03/2024	ASCV TYU T566	AMERN	3.00	120.00	360.00				16/03/2024	ASCV TYU T566	10.00	120.00	100.00
9										16/03/2024	ASCV TYU T566	5.00	120.00	110.00
10
11
12
saa

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

doesn't split data when there is no COMPLETE word.
I'm waiting for your comment.

 

[Sam_D_Ben]

Ok, Please insert a new Module and Paste this code and do Alt+F8 run "SplitDataAndDeductFromAndUpdate" AND remove previous modules. It is working for me, did lot of trial and error with the limited data that been provided.

Option Explicit

Sub SplitDataAndDeductFromAndUpdate()
    Call SplitDataAndDeductFromAndUpdateJ2M
    Call UpdateSAA_ColumnCCPrice
    Call UpdateSAA_ColumnO
End Sub

Sub SplitDataAndDeductFromAndUpdateJ2M()
    Dim wsSAA As Worksheet, wsSS As Worksheet, wsStock As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim i As Long, j As Long, k As Long
    Dim SAA_ID As String, SAA_Date As Variant
    Dim SAA_Qty As Double
    Dim remQty As Double, allocQty As Double, availQty As Double
    Dim outRow As Long
    Dim unitPrice As Double, ssPrice As Double
    Dim matchID As String, price As Double
    Dim found As Boolean
    Dim lastRowSAA_K As Long
    Dim matchDate As Variant
    
    Set wsSAA = ThisWorkbook.Sheets("SAA")
    Set wsSS = ThisWorkbook.Sheets("SS")
    Set wsStock = ThisWorkbook.Sheets("STOCK")
    
    If IsEmpty(wsSAA.Cells(1, "G").Value) Then
        wsSAA.Cells(1, "G").Value = "NOTICE"
    End If
    
    lastRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "A").End(xlUp).Row
    lastRowStock = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row
    lastRowSS = wsSS.Cells(wsSS.Rows.Count, "J").End(xlUp).Row
    
    outRow = wsSAA.Cells(wsSAA.Rows.Count, "J").End(xlUp).Row
    If outRow < 2 Then
        outRow = 2
    Else
        outRow = outRow + 1
    End If
    
    For i = 2 To lastRowSAA
        If Trim(wsSAA.Cells(i, "G").Value) = "Complete" Then GoTo NextRow
        
        SAA_Date = wsSAA.Cells(i, "A").Value
        SAA_ID = Trim(wsSAA.Cells(i, "B").Value)
        SAA_Qty = wsSAA.Cells(i, "D").Value
        
        If SAA_Qty <= 0 Then GoTo NextRow
        remQty = SAA_Qty
        
        For j = 2 To lastRowStock
            If Trim(wsStock.Cells(j, "B").Value) = SAA_ID And _
               wsStock.Cells(j, "C").Value > 0 And _
               wsStock.Cells(j, "A").Value < SAA_Date Then
               
                availQty = wsStock.Cells(j, "C").Value
                allocQty = IIf(availQty >= remQty, remQty, availQty)
                
                wsStock.Cells(j, "C").Value = availQty - allocQty
                remQty = remQty - allocQty
                
                unitPrice = wsStock.Cells(j, "E").Value
                
                wsSAA.Cells(outRow, "J").Value = SAA_Date
                wsSAA.Cells(outRow, "K").Value = SAA_ID
                wsSAA.Cells(outRow, "L").Value = allocQty
                outRow = outRow + 1
                
                If remQty <= 0 Then Exit For
            End If
        Next j
        
        If remQty > 0 Then
            For k = 2 To lastRowSS
                If Trim(wsSS.Cells(k, "K").Value) = SAA_ID And _
                   wsSS.Cells(k, "L").Value > 0 And _
                   wsSS.Cells(k, "J").Value < SAA_Date Then
                    
                    availQty = wsSS.Cells(k, "L").Value
                    allocQty = IIf(availQty >= remQty, remQty, availQty)
                    
                    wsSS.Cells(k, "L").Value = availQty - allocQty
                    remQty = remQty - allocQty
                    
                    ssPrice = wsSS.Cells(k, "N").Value
                    
                    wsSAA.Cells(outRow, "J").Value = SAA_Date
                    wsSAA.Cells(outRow, "K").Value = SAA_ID
                    wsSAA.Cells(outRow, "L").Value = allocQty
                    wsSAA.Cells(outRow, "M").Value = ssPrice
                    outRow = outRow + 1
                    
                    If remQty <= 0 Then Exit For
                End If
            Next k
        End If
        
NextRow:
        wsSAA.Cells(i, "G").Value = "Complete"
    Next i
    
    lastRowSAA_K = wsSAA.Cells(wsSAA.Rows.Count, "K").End(xlUp).Row
    
    For i = 2 To lastRowSAA_K
        matchID = Trim(wsSAA.Cells(i, "K").Value)
        matchDate = wsSAA.Cells(i, "J").Value
        found = False
        
        For j = 2 To lastRowSAA
            If Trim(wsSAA.Cells(j, "B").Value) = matchID And wsSAA.Cells(j, "A").Value = matchDate Then
                price = wsSAA.Cells(j, "E").Value
                wsSAA.Cells(i, "M").Value = price
                found = True
                Exit For
            End If
        Next j
        
        If Not found Then
            wsSAA.Cells(i, "M").Value = "Price not found"
        End If
    Next i
    
    wsSAA.Range("J2:J" & lastRowSAA_K).NumberFormat = "dd/mm/yyyy"
    MsgBox "Processing complete!"
End Sub

Sub UpdateSAA_ColumnCCPrice()
    Dim wsSAA As Worksheet, wsStock As Worksheet, wsSS As Worksheet
    Dim lastRowSAA As Long, lastRowStock As Long, lastRowSS As Long
    Dim dictPrices As Object
    Dim i As Long, j As Long
    Dim SAA_ID As String
    Dim priceList As Collection
    Dim priceIndex As Integer
    Dim k As Long
    Dim foundPrice As Boolean
    Dim m As Long
    Dim matched As Boolean
    
    Set wsSAA = ThisWorkbook.Sheets("SAA")
    Set wsStock = ThisWorkbook.Sheets("STOCK")
    Set wsSS = ThisWorkbook.Sheets("SS")
    
    lastRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "K").End(xlUp).Row
    lastRowStock = wsStock.Cells(wsStock.Rows.Count, "B").End(xlUp).Row
    lastRowSS = wsSS.Cells(wsSS.Rows.Count, "K").End(xlUp).Row
    
    Set dictPrices = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRowStock
        SAA_ID = Trim(wsStock.Cells(i, "B").Value)
        If SAA_ID <> "" Then
            If Not dictPrices.Exists(SAA_ID) Then
                Set priceList = New Collection
                dictPrices.Add SAA_ID, priceList
            Else
                Set priceList = dictPrices(SAA_ID)
            End If
            priceList.Add wsStock.Cells(i, "D").Value
        End If
    Next i
    
    For i = 2 To lastRowSS
        SAA_ID = Trim(wsSS.Cells(i, "K").Value)
        If SAA_ID <> "" Then
            If Not dictPrices.Exists(SAA_ID) Then
                Set priceList = New Collection
                dictPrices.Add SAA_ID, priceList
            Else
                Set priceList = dictPrices(SAA_ID)
            End If
            priceList.Add wsSS.Cells(i, "M").Value
        End If
    Next i
    
    For i = 2 To lastRowSAA
        SAA_ID = Trim(wsSAA.Cells(i, "K").Value)
        
        If dictPrices.Exists(SAA_ID) Then
            Set priceList = dictPrices(SAA_ID)
            priceIndex = Application.CountIf(wsSAA.Range("K2:K" & i), SAA_ID) - 1
            
            If priceIndex <= priceList.Count - 1 Then
                wsSAA.Cells(i, "N").Value = priceList.Item(priceIndex + 1)
            Else
                foundPrice = False
                For k = i - 1 To 2 Step -1
                    If Trim(wsSAA.Cells(k, "K").Value) = SAA_ID Then
                        wsSAA.Cells(i, "N").Value = wsSAA.Cells(k, "N").Value
                        foundPrice = True
                        Exit For
                    End If
                Next k
                If Not foundPrice Then
                    wsSAA.Cells(i, "N").Value = priceList.Item(priceList.Count)
                End If
            End If
        Else
            matched = False
            For m = i - 1 To 2 Step -1
                If Trim(wsSAA.Cells(m, "K").Value) = SAA_ID Then
                    wsSAA.Cells(i, "N").Value = wsSAA.Cells(m, "N").Value
                    matched = True
                    Exit For
                End If
            Next m
            If Not matched Then
                wsSAA.Cells(i, "N").Value = ""
            End If
        End If
    Next i
    
    MsgBox "Column N updated successfully!"
End Sub


Sub UpdateSAA_ColumnO()
    Dim wsSAA As Worksheet
    Dim lastRowSAA As Long
    Dim i As Long
    Dim result As Double
    
    Set wsSAA = ThisWorkbook.Sheets("SAA")
    
    lastRowSAA = wsSAA.Cells(wsSAA.Rows.Count, "N").End(xlUp).Row
    
    For i = 2 To lastRowSAA
        If Not IsEmpty(wsSAA.Cells(i, "M").Value) And _
           Not IsEmpty(wsSAA.Cells(i, "N").Value) And _
           Not IsEmpty(wsSAA.Cells(i, "L").Value) Then
           
            result = (wsSAA.Cells(i, "M").Value - wsSAA.Cells(i, "N").Value) * wsSAA.Cells(i, "L").Value
            wsSAA.Cells(i, "O").Value = result
        End If
    Next i
End Sub

 

[Omar M]

seem to be problem
check highlighted yellow rows as new data

1om.xlsm
	J	K	L	M	N
1	DATE	ID	QTY	UNIT PRICE	TOTAL
2	01/03/2024	AVSD HJY00	1.00	222.00	4,884.00
3	02/03/2024	AZSDC	0.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	0.00	100.00	1,000.00
6	05/03/2024	AZSDC	4.00	123.00	615.00
7	06/03/2024	ASCV TYU T566	0.00	110.00	550.00
8	07/03/2024	AZSDC	6.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
13	13/03/2024	ASCV TYU T566	10.00	115	1150
14	14/03/2024	ASCV TYU T566	11.00	117.00	1,287.00
SS

Cell Formulas
Range	Formula
N13:N14	N13	=L13*M13

1om.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	ID	NAME	QTY	PRICE	TOTAL	NOTICE			DATE	ID	QTY	SS PRICE	CC PRICE	TOTAL
2	11/03/2024	AVSD HJY00	amren	28.00	250.00	7,000.00	Complete			11/03/2024	AVSD HJY00	4.00	250.00	228.00	88.00
3	12/03/2024	AZSDC	OMRAN	5.00	130.00	650.00	Complete			11/03/2024	AVSD HJY00	24.00	250.00	222.00	672.00
4	13/03/2024	AVSD HJY00	amuri	3.00	260.00	780.00	Complete			12/03/2024	AZSDC	4.00	130.00	123.00	28.00
5	14/03/2024	AZSDC	OMRAN	6.00	140.00	840.00	Complete			12/03/2024	AZSDC	1.00	130.00	120.00	10.00
6	16/03/2024	ASCV TYU T566	AMERN	1.00	120.00	120.00	Complete			13/03/2024	AVSD HJY00	3.00	260.00	223.00	111.00
7	17/03/2024	ASCV TYU T566	AMERN	10.00	140.00	1,400.00				14/03/2024	AZSDC	6.00	140.00	123.00	102.00
8	18/03/2024	ASCV TYU T566	AMERN	10.00	150.00	1,500.00				16/03/2024	ASCV TYU T566	10.00	120.00	155.00	-350.00
9	19/03/2024	AZSDC	OMRAN	9.00	144.00	1,296.00				16/03/2024	ASCV TYU T566	5.00	120.00	100.00	100.00
saa

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

error in prices

1om.xlsm
	J	K	L	M	N	O
1	DATE	ID	QTY	SS PRICE	CC PRICE	TOTAL
2	11/03/2024	AVSD HJY00	4.00	250.00	228.00	88.00
3	11/03/2024	AVSD HJY00	24.00	250.00	222.00	672.00
4	12/03/2024	AZSDC	4.00	130.00	123.00	28.00
5	12/03/2024	AZSDC	1.00	130.00	120.00	10.00
6	13/03/2024	AVSD HJY00	3.00	260.00	223.00	111.00
7	14/03/2024	AZSDC	6.00	140.00	123.00	102.00
8	16/03/2024	ASCV TYU T566	10.00	120.00	155.00	-350.00
9	16/03/2024	ASCV TYU T566	5.00	120.00	100.00	100.00
10	17/03/2024	ASCV TYU T566	10.00	140.00	110.00	300.00
11	18/03/2024	ASCV TYU T566	10.00	150.00	115.00	350.00
12	19/03/2024	AZSDC	4.00	144.00	124.00	80.00
13	19/03/2024	AZSDC	5.00	144.00	124.00	100.00
saa

should be 115,117 not 110,115 , also should be 123,124 , not 124,124
also if add new data in STOCK sheet for same ID is existed in SS,SAA sheet then will ignore price from STOCK sheet when split data!
last thing as in OP after QTY=0 then should TOTAL column =ZERO for ID contains zero for QTY into two sheets SS,STOCK.

 

[Sam_D_Ben]

Ok, Please check, whenever you add more data to Stock and SS sheet, and before running the code please clear values in N and O Colomn in SAA sheet to check if you are getting the right values.

 

[Omar M]

same problem , sorry!