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

Omar M

Board Regular
Joined
Jan 11, 2024
Messages
86
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 .
 
when add new data in A:G for saa sheet will not split in J:Q .
G cells corresponding to new data be blank and if there is no enough quantity in Stock and SS sheet splitting will not happen.
 
Upvote 0
G cells corresponding to new data be blank and if there is no enough quantity in Stock and SS sheet splitting will not happen.
1om.xlsm
ABCDEFG
1DATEIDNAMEQTYPRICETOTALNOTICE
211/03/2024AVSD HJY00amren28.00250.007,000.00Processed
312/03/2024AZSDCOMRAN5.00130.00650.00Processed
413/03/2024AVSD HJY00amuri3.00260.00780.00Processed
514/03/2024AZSDCOMRAN6.00140.00840.00Processed
616/03/2024ASCV TYU T566AMERN1.00120.00120.00Processed
717/03/2024AZSDCOMRAN6.00155.00930.00Processed
saa
Cell Formulas
RangeFormula
F2:F7F2=D2*E2


should split for 6 QTY for AZSDC ID
available about 10 QTY in SS sheet
1om.xlsm
HIJKLM
1DATEIDQTYUNIT PRICETOTAL
201/03/2024AVSD HJY000.00222.000.00Processed
302/03/2024AZSDC3.00120.00360.00
403/03/2024AVSD HJY005.00223.001,115.00
503/03/2024ASCV TYU T5669.00100.00900.00
605/03/2024AZSDC5.00123.00492.00
706/03/2024ASCV TYU T5665.00110.00550.00
807/03/2024AZSDC2.00124.00248.00
907/03/2024BBSDFRT-004.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
 
Upvote 0
should split for 6 QTY for AZSDC ID
available about 10 QTY in SS sheet
It is working fine for me. I checked AZSDC ID seperately, Please close this thread and have new thread for additions or corrections from others as well. I believe I answered your orginal post. Thank You:)

1740316957682.png
 
Upvote 0
sorry buddy!
this is not fixed my problem and doesn't work for me and add many sheets in every time it's not good idea so the last version doesn't work for me I no know how work for you !!!
add new thread is not good idea, I accept from moderator will close it because will consider duplicate thread .
maybe I post in other forum.
anyway thank you for everything.
 
Upvote 0

Forum statistics

Threads
1,226,858
Messages
6,193,385
Members
453,792
Latest member
Vic001

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