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

O

Omar M

Board Regular
Joined
Jan 11, 2024
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
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
JKLMN
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY0022.00222.004,884.00
302/03/2024AZSDC10.00120.001,200.00
403/03/2024AVSD HJY0010.00223.002,230.00
503/03/2024ASCV TYU T56610.00100.001,000.00
605/03/2024AZSDC5.00123.00615.00
706/03/2024ASCV TYU T5665.00110.00550.00
807/03/2024AZSDC2.00124.00248.00
907/03/2024BBSDFRT-001.00120.00120.00
1008/03/2024BBSDFRT-012.00124.00248.00
1110/03/2024VBGHYT 34YTY 662.00220.00440.00
1212/03/2024VBGHYT 34YTY 662.00225.00450.00
SS




1om.xlsm
ABCDEFGHIJKLMNO
1DATEIDNAMEQTYPRICETOTAL
211/03/2024AVSD HJY00amren28.00250.007,000.00
312/03/2024AZSDCOMRAN5.00130.00650.00
413/03/2024AVSD HJY00amuri3.00260.00780.00
514/03/2024AZSDCOMRAN6.00140.00840.00
616/03/2024ASCV TYU T566AMERN15.00120.001,800.00
7
8
9
10
saa
Cell Formulas
RangeFormula
F2:F6F2=D2*E2



1om.xlsm
ABCDE
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY004.00228.00912.00
302/03/2024AVSD HJY0110.00120.001,200.00
403/03/2024AVSD HJY0210.0090.00900.00
505/03/2024AZSDC4.00123.00492.00
607/03/2024BBSDFRT-001.00120.00120.00
708/03/2024BBSDFRT-012.00124.00248.00
810/03/2024BBSDFRT-022.00220.00440.00
912/03/2024BBSDFRT-032.00225.00450.00
stock




result in ssa sheet
1om.xlsm
ABCDEFGHIJKLMNO
1DATEIDNAMEQTYPRICETOTAL
211/03/2024AVSD HJY00amren28.00250.007,000.00
312/03/2024AZSDCOMRAN5.00130.00650.00
413/03/2024AVSD HJY00amuri3.00260.00780.00DATEIDQTYSS PRICECC PRICETOTAL
514/03/2024AZSDCOMRAN6.00140.00840.0011/03/2024AVSD HJY004250.00228.0088.00
616/03/2024ASCV TYU T566AMERN15.00120.001,800.0011/03/2024AVSD HJY0022.00250.00222.00616.00
711/03/2024AVSD HJY002.00250.00223.0054.00
812/03/2024AZSDC4.00130.00123.0028.00
912/03/2024AZSDC1.00130.00120.0010.00
1013/03/2024AVSD HJY003.00260.00223.00111.00
1114/03/2024AZSDC6.00140.00120.00120.00
1216/03/2024ASCV TYU T56610.00120.00100.00200.00
1316/03/2024ASCV TYU T5665.00120.00110.0050.00
saa
Cell Formulas
RangeFormula
F2:F6F2=D2*E2
O5:O13O5=(M5-N5)*L5


and result in
column L,C and column N,E for SS,STOCK sheet .
1om.xlsm
JKLMN
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY000.00222.000.00
302/03/2024AZSDC3.00120.001,200.00
403/03/2024AVSD HJY005.00223.002,230.00
503/03/2024ASCV TYU T5660.00100.000.00
605/03/2024AZSDC5.00123.00615.00
706/03/2024ASCV TYU T5660.00110.000.00
807/03/2024AZSDC2.00124.00248.00
907/03/2024BBSDFRT-001.00120.00120.00
1008/03/2024BBSDFRT-012.00124.00248.00
1110/03/2024VBGHYT 34YTY 662.00220.00440.00
1212/03/2024VBGHYT 34YTY 662.00225.00450.00
SS



1om.xlsm
ABCDE
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY000.00228.000.00
302/03/2024AVSD HJY0110.00120.001,200.00
403/03/2024AVSD HJY0210.0090.00900.00
505/03/2024AZSDC0.00123.000.00
607/03/2024BBSDFRT-001.00120.00120.00
708/03/2024BBSDFRT-012.00124.00248.00
810/03/2024BBSDFRT-022.00220.00440.00
912/03/2024BBSDFRT-032.00225.00450.00
stock
Cell Formulas
RangeFormula
E2:E9E2=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 .
 
Sort by date Sort by votes
You mind explaining,
DATEIDNAMEQTYPRICETOTAL
03/11/24AVSD HJY00amren282507000

Ref below: What is the criteria of splitting
DATEIDQTYSS PRICECC PRICETOTAL
03/11/24AVSD HJY00425022888
03/11/24AVSD HJY0022250222616
03/11/24AVSD HJY00225022354

_AND the SS table,
DATEIDQTYUNIT PRICETOTAL
03/01/24AVSD HJY00222224884

_got changed to-
DATEIDQTYUNIT PRICETOTAL
03/01/24AVSD HJY0002220

_AND the Stock table,
DATEIDQTYUNIT PRICETOTAL
03/01/24AVSD HJY004228912

_got changed to,
DATEIDQTYUNIT PRICETOTAL
03/01/24AVSD HJY0002280

Thank You
 
Upvote 0
Hi Sam
I expected my thread is not enough clear.
pic1.JPG


so when split 28 QTY will be into three rows by repeat date and id and price
in STOCK sheet contains only 4 and 22 contains in SS sheet and I need 2 to reach 28 then will move next row for the same ID

after split
pic2.JPG


as you see will subtract from SS sheet for the same ID and old date
pic3.JPG


and will subtract 4 from STOCK sheet for the same ID after split for old date.
did you read this

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.
Click to expand...
here you soon.
 
Upvote 0
May be something like this,

Output range is - SAA sheet!J1:O10, for to help you rewrite it in your own way i made it to two different parts, first it will find the calculated values for column J-M and the second one for colum M-O. Please check if this is working, If then_

<I am hoping help from coders to have a compact version of it. ---I tried simplifying it, but horrible>

VBA Code: 
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, lastRowSSA As Long
    Dim matchDate As Variant

    Set wsSSA = ThisWorkbook.Sheets("SAA")
    Set wsSS = ThisWorkbook.Sheets("SS")
    Set wsStock = ThisWorkbook.Sheets("STOCK")

    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
    lastRowSAA_K = wsSSA.Cells(wsSSA.Rows.Count, "K").End(xlUp).Row
    wsSSA.Range("J2:O" & wsSSA.Rows.Count).ClearContents
    
    outRow = 2
    
    For i = 2 To lastRowSAA
        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 NextSAA
        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

NextSAA:
    Next i

    For i = 2 To outRow - 1
        wsSSA.Cells(i, "M").Value = wsSSA.Cells(i, "E").Value
    Next i

    wsSSA.Range("J2:J" & outRow - 1).NumberFormat = "dd/mm/yyyy"
    
    lastRowSSA = wsSSA.Cells(wsSSA.Rows.Count, "A").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
    
    MsgBox "Processing complete!"
End Sub
VBA Code: 
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 lastMatchRow As Long
    
    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
                For j = i - 1 To 2 Step -1
                    If Trim(wsSSA.Cells(j, "K").Value) = SAA_ID Then
                        lastMatchRow = j
                        priceIndex = Application.CountIf(wsSSA.Range("K2:K" & lastMatchRow), SAA_ID) - 1
                        Exit For
                    End If
                Next j
            End If
            
            If priceIndex <= priceList.Count - 1 Then
                wsSSA.Cells(i, "N").Value = priceList.Item(priceIndex + 1)
            Else
                wsSSA.Cells(i, "N").Value = "Not Found"
            End If
        Else
            wsSSA.Cells(i, "N").Value = "Not Found"
        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
 
Upvote 0
thanks for writing all of theses codes for me.
I will check it, comeback soon.;)
 
Last edited:
Upvote 0
there are missed price column (N) and values also formula to calculation in column O .
should brings also prices across ranges when split data as I did it.

you can see result
1om.xlsm
JKLMN
1DATEIDQTYUNIT PRICETOTAL
211/03/2024AVSD HJY004.00250.00
311/03/2024AVSD HJY0022.00130.00
411/03/2024AVSD HJY002.00260.00
512/03/2024AZSDC4.00140.00
612/03/2024AZSDC1.00120.00
713/03/2024AVSD HJY003.00
814/03/2024AZSDC6.00
916/03/2024ASCV TYU T56610.00
1016/03/2024ASCV TYU T5665.00
11
saa



and compare with above.
by the way I realized there are something wrong in my project
should cancel this line

Rich (BB code): 
wsSSA.Range("J2:O" & wsSSA.Rows.Count).ClearContents
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.
1om.xlsm
ABCDEFG
1DATEIDNAMEQTYPRICETOTALNOTICE
211/03/2024AVSD HJY00amren28.00250.007,000.00COMPLETE
312/03/2024AZSDCOMRAN5.00130.00650.00COMPLETE
413/03/2024AVSD HJY00amuri3.00260.00780.00COMPLETE
514/03/2024AZSDCOMRAN6.00140.00840.00COMPLETE
616/03/2024ASCV TYU T566AMERN15.00120.001,800.00COMPLETE
saa
Cell Formulas
RangeFormula
F2:F6F2=D2*E2

thanks again.
 
Upvote 0
First code should work properly, Please check it again, provides SS, SAA, Stock tables are in the same location. And the second code find values for N and O.
 
Upvote 0
I'm sure for the same data
1om.xlsm
JKLMN
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY0022.00222.004,884.00
302/03/2024AZSDC10.00120.001,200.00
403/03/2024AVSD HJY0010.00223.002,230.00
503/03/2024ASCV TYU T56610.00100.001,000.00
605/03/2024AZSDC5.00123.00615.00
706/03/2024ASCV TYU T5665.00110.00550.00
807/03/2024AZSDC2.00124.00248.00
907/03/2024BBSDFRT-001.00120.00120.00
1008/03/2024BBSDFRT-012.00124.00248.00
1110/03/2024VBGHYT 34YTY 662.00220.00440.00
1212/03/2024VBGHYT 34YTY 662.00225.00450.00
SS


1om.xlsm
ABCDEFG
1DATEIDNAMEQTYPRICETOTALNOTICE
211/03/2024AVSD HJY00amren28.00250.007,000.00COMPLETE
312/03/2024AZSDCOMRAN5.00130.00650.00COMPLETE
413/03/2024AVSD HJY00amuri3.00260.00780.00COMPLETE
514/03/2024AZSDCOMRAN6.00140.00840.00COMPLETE
616/03/2024ASCV TYU T566AMERN15.00120.001,800.00COMPLETE
saa
Cell Formulas
RangeFormula
F2:F6F2=D2*E2








1om.xlsm
ABCDE
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY004.00228.00912.00
302/03/2024AVSD HJY0110.00120.001,200.00
403/03/2024AVSD HJY0210.0090.00900.00
505/03/2024AZSDC4.00123.00492.00
607/03/2024BBSDFRT-001.00120.00120.00
708/03/2024BBSDFRT-012.00124.00248.00
810/03/2024BBSDFRT-022.00220.00440.00
912/03/2024BBSDFRT-032.00225.00450.00
stock






here is what I got again.
1om.xlsm
JKLMN
1DATEIDQTYUNIT PRICETOTAL
211/03/2024AVSD HJY004.00250.00
311/03/2024AVSD HJY0022.00130.00
411/03/2024AVSD HJY002.00260.00
512/03/2024AZSDC4.00140.00
612/03/2024AZSDC1.00120.00
713/03/2024AVSD HJY003.00
814/03/2024AZSDC6.00
916/03/2024ASCV TYU T56610.00
1016/03/2024ASCV TYU T5665.00
11
saa
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Macro to delete duplicates ID contains same price for 1000 elements
Replies
3
Views
61
Peter_SSs
Peter_SSs
L
  • Question Question
replace data for multiple sheets from source file to destination file based on back up(date,time)
Replies
1
Views
679
leap out
L
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
179
Omar M
O
R
  • Question Question
Data required in User Form Box based on selection
Replies
2
Views
140
RAMU
R
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
209
kevin9999
K

Forum statistics

Threads
1,226,850
Messages
6,193,358
Members
453,790
Latest member
yassinosnoo1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top