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)
result in ssa sheet
and result in
column L,C and column N,E for SS,STOCK sheet .
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 .
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 .
