Hello ,
I would macro (nothing else) Merge QTY for ATTR for ID contains the same price and compare with the same ID for the same price in AMMR sheet then will subtract QTY in AMMR sheet from QTY in ATTR sheet for the same ID and same price.
the merging QTY will just be for ATTR . don't merge QTY for AMMR sheet .
so when subtract the QTY will search for duplicates ID in AMMR sheet until subtract the whole QTY based on ATTR sheet
after that will populate OK word for adjacent cell in BALANCE column in ATTR sheet and any ID contains OK word next time ignore from subtraction.
the same thing for AVSR,SSERT sheets should subtract QTY for duplicate ID in AVSR sheet from merging QTY in SSERT sheet.
after subtraction will be zero QTY for some ID in AMMR,AVSR sheets then shouldn't subtract from zero until doesn't show minus value , just ignore zero QTY for some ID when try again subtract when add new data in ATTR,SSERT
IMPORTANT NOTICE: when try subtract QTY and and doesn't cover whole QTY from the first ID then the remaining subtract form the next duplicate ID until finishing QTY based on ATTR or SSERT sheets.
ex: SDCMN 1000A for price=110 in AMMR sheet =15 ,10 QTY(rows2,13)
and ATTR sheet will be 16 so first subtract 15 from row2 and 1 from row 13 to become =9 and when subtract 2 in row6 from ATTR sheet to become final =7.
without for get change amounts in column BALANCE=QTY*PRICE
should be like this
thanks in advanced
I would macro (nothing else) Merge QTY for ATTR for ID contains the same price and compare with the same ID for the same price in AMMR sheet then will subtract QTY in AMMR sheet from QTY in ATTR sheet for the same ID and same price.
the merging QTY will just be for ATTR . don't merge QTY for AMMR sheet .
so when subtract the QTY will search for duplicates ID in AMMR sheet until subtract the whole QTY based on ATTR sheet
after that will populate OK word for adjacent cell in BALANCE column in ATTR sheet and any ID contains OK word next time ignore from subtraction.
the same thing for AVSR,SSERT sheets should subtract QTY for duplicate ID in AVSR sheet from merging QTY in SSERT sheet.
after subtraction will be zero QTY for some ID in AMMR,AVSR sheets then shouldn't subtract from zero until doesn't show minus value , just ignore zero QTY for some ID when try again subtract when add new data in ATTR,SSERT
IMPORTANT NOTICE: when try subtract QTY and and doesn't cover whole QTY from the first ID then the remaining subtract form the next duplicate ID until finishing QTY based on ATTR or SSERT sheets.
ex: SDCMN 1000A for price=110 in AMMR sheet =15 ,10 QTY(rows2,13)
and ATTR sheet will be 16 so first subtract 15 from row2 and 1 from row 13 to become =9 and when subtract 2 in row6 from ATTR sheet to become final =7.
without for get change amounts in column BALANCE=QTY*PRICE
| AMER.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 08/01/2024 | AMMR | SDCMN 1000A | AASLN# | 15.00 | 110.00 | 1,650.00 | ||
| 3 | 08/01/2024 | AMMR | CVVBGT-100 | AASLN# | 20.00 | 115.00 | 2,300.00 | ||
| 4 | 09/01/2024 | AMER | CVVBGT-101 | AQQWNY# | 10.00 | 111.00 | 1,110.00 | ||
| 5 | 09/01/2024 | AMER | A111-300B | AQQWNY# | 5.00 | 100.00 | 500.00 | ||
| 6 | 09/01/2024 | AMER | A112-300B | AQQWNY# | 22.00 | 114.00 | 2,508.00 | ||
| 7 | 10/01/2024 | AMRAAV | SDCMN 1000A | AZZXX# | 10.00 | 112.00 | 1,120.00 | ||
| 8 | 10/01/2024 | AMRAAV | CVVBGT-100 | AZZXX# | 5.00 | 113.00 | 565.00 | ||
| 9 | 11/01/2024 | ASMAN | CVVBGT-100 | AFFXX# | 4.00 | 117.00 | 468.00 | ||
| 10 | 12/01/2024 | ASMAN | A111-300B | AASFXX# | 10.00 | 112.00 | 1,120.00 | ||
| 11 | 13/01/2024 | AMRAAV | QWW-900O10 | AQWERR# | 5.00 | 140.00 | 700.00 | ||
| 12 | 13/01/2024 | AMRAAV | QWW-900O11 | AQWERR# | 20.00 | 145.00 | 2,900.00 | ||
| 13 | 13/01/2024 | AMRAAV | SDCMN 1000A | AQWERR# | 10.00 | 110.00 | 1,100.00 | ||
AMMR | |||||||||
| AMER.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 08/01/2024 | RVVTT | A111-300B | SLLNO-00 | 2.00 | 120.00 | 240.00 | ||
| 3 | 09/01/2024 | RTSS | SDCMN 1000A | SLLNO-01 | 2.00 | 144.00 | 288.00 | ||
| 4 | 09/01/2024 | RTSS | CVVBGT-100 | SLLNO-01 | 2.00 | 143.00 | 286.00 | ||
| 5 | 10/01/2024 | ARTTT | SDCMN 1000A | SLLNO-02 | 5.00 | 140.00 | 700.00 | ||
| 6 | 10/01/2024 | ARTTT | CVVBGT-100 | SLLNO-02 | 5.00 | 135.00 | 675.00 | ||
| 7 | 10/01/2024 | ARTTT | CVVBGT-101 | SLLNO-02 | 5.00 | 137.00 | 685.00 | ||
| 8 | 11/01/2024 | RTYY | A111-300B | SLLNO-03 | 3.00 | 125.00 | 375.00 | ||
| 9 | 11/01/2024 | RVVTT | A111-300B | SLLNO-00 | 4.00 | 120.00 | 480.00 | ||
AVSR | |||||||||
| AMER.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 13/01/2024 | AMMR | SDCMN 1000A | TTRR-00 | 16.00 | 110.00 | 1,760.00 | ||
| 3 | 14/01/2024 | AMMR | CVVBGT-100 | TTRR-01 | 5.00 | 115.00 | 575.00 | ||
| 4 | 14/01/2024 | AMER | CVVBGT-101 | TTRR-01 | 2.00 | 111.00 | 222.00 | ||
| 5 | 15/01/2024 | ASMAN | SDCMN 1000A | TTRR-02 | 5.00 | 112.00 | 560.00 | ||
| 6 | 16/01/2024 | AMMR | SDCMN 1000A | TTRR-03 | 2.00 | 110.00 | 220.00 | ||
| 7 | 17/01/2024 | ASMAN | SDCMN 1000A | TTRR-04 | 1.00 | 112.00 | 112.00 | ||
ATTR | |||||||||
| AMER.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 14/01/2024 | RVVTT | A111-300B | RSTTT-00 | 4.00 | 120.00 | 480.00 | ||
| 3 | 15/01/2024 | RTSS | SDCMN 1000A | RSTTT-01 | 2.00 | 144.00 | 288.00 | ||
| 4 | 16/01/2024 | RVVTT | A111-300B | RSTTT-02 | 1.00 | 120.00 | 120.00 | ||
| 5 | 17/01/2024 | RTSS | CVVBGT-100 | RSTTT-03 | 2.00 | 143.00 | 286.00 | ||
| 6 | 18/01/2024 | ARTTT | SDCMN 1000A | RSTTT-04 | 2.00 | 140.00 | 280.00 | ||
| 7 | 19/01/2024 | ARTTT | SDCMN 1000A | RSTTT-05 | 2.00 | 140.00 | 280.00 | ||
SSERT | |||||||||
should be like this
| AMER1.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 08/01/2024 | AMMR | SDCMN 1000A | AASLN# | 0.00 | 110.00 | 0.00 | ||
| 3 | 08/01/2024 | AMMR | CVVBGT-100 | AASLN# | 15.00 | 115.00 | 1,725.00 | ||
| 4 | 09/01/2024 | AMER | CVVBGT-101 | AQQWNY# | 8.00 | 111.00 | 888.00 | ||
| 5 | 09/01/2024 | AMER | A111-300B | AQQWNY# | 5.00 | 100.00 | 500.00 | ||
| 6 | 09/01/2024 | AMER | A112-300B | AQQWNY# | 22.00 | 114.00 | 2,508.00 | ||
| 7 | 10/01/2024 | AMRAAV | SDCMN 1000A | AZZXX# | 4.00 | 112.00 | 448.00 | ||
| 8 | 10/01/2024 | AMRAAV | CVVBGT-100 | AZZXX# | 5.00 | 113.00 | 565.00 | ||
| 9 | 11/01/2024 | ASMAN | CVVBGT-100 | AFFXX# | 4.00 | 117.00 | 468.00 | ||
| 10 | 12/01/2024 | ASMAN | A111-300B | AASFXX# | 10.00 | 112.00 | 1,120.00 | ||
| 11 | 13/01/2024 | AMRAAV | QWW-900O10 | AQWERR# | 5.00 | 140.00 | 700.00 | ||
| 12 | 13/01/2024 | AMRAAV | QWW-900O11 | AQWERR# | 20.00 | 145.00 | 2,900.00 | ||
| 13 | 13/01/2024 | AMRAAV | SDCMN 1000A | AQWERR# | 7.00 | 110.00 | 770.00 | ||
AMMR | |||||||||
| AMER1.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | ||
| 2 | 08/01/2024 | RVVTT | A111-300B | SLLNO-00 | 0.00 | 120.00 | 0.00 | ||
| 3 | 09/01/2024 | RTSS | SDCMN 1000A | SLLNO-01 | 0.00 | 144.00 | 0.00 | ||
| 4 | 09/01/2024 | RTSS | CVVBGT-100 | SLLNO-01 | 0.00 | 143.00 | 286.00 | ||
| 5 | 10/01/2024 | ARTTT | SDCMN 1000A | SLLNO-02 | 1.00 | 140.00 | 700.00 | ||
| 6 | 10/01/2024 | ARTTT | CVVBGT-100 | SLLNO-02 | 5.00 | 135.00 | 675.00 | ||
| 7 | 10/01/2024 | ARTTT | CVVBGT-101 | SLLNO-02 | 5.00 | 137.00 | 685.00 | ||
| 8 | 11/01/2024 | RTYY | A111-300B | SLLNO-03 | 3.00 | 125.00 | 375.00 | ||
| 9 | 11/01/2024 | RVVTT | A111-300B | SLLNO-00 | 2.00 | 120.00 | 240.00 | ||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 | |||||||||
AVSR | |||||||||
| AMER1.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | |||
| 2 | 13/01/2024 | AMMR | SDCMN 1000A | TTRR-00 | 16.00 | 110.00 | 1,760.00 | ok | ||
| 3 | 14/01/2024 | AMMR | CVVBGT-100 | TTRR-01 | 5.00 | 115.00 | 575.00 | ok | ||
| 4 | 14/01/2024 | AMER | CVVBGT-101 | TTRR-01 | 2.00 | 111.00 | 222.00 | ok | ||
| 5 | 15/01/2024 | ASMAN | SDCMN 1000A | TTRR-02 | 5.00 | 112.00 | 560.00 | ok | ||
| 6 | 16/01/2024 | AMMR | SDCMN 1000A | TTRR-03 | 2.00 | 110.00 | 220.00 | ok | ||
| 7 | 17/01/2024 | ASMAN | SDCMN 1000A | TTRR-04 | 1.00 | 112.00 | 112.00 | ok | ||
ATTR | ||||||||||
| AMER1.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CLIENT | BRAND NO | ORDER NO | QTY | UNIT PRICE | BALANCE | |||
| 2 | 14/01/2024 | RVVTT | A111-300B | RSTTT-00 | 4.00 | 120.00 | 480.00 | ok | ||
| 3 | 15/01/2024 | RTSS | SDCMN 1000A | RSTTT-01 | 2.00 | 144.00 | 288.00 | ok | ||
| 4 | 16/01/2024 | RVVTT | A111-300B | RSTTT-02 | 1.00 | 120.00 | 120.00 | ok | ||
| 5 | 17/01/2024 | RTSS | CVVBGT-100 | RSTTT-03 | 2.00 | 143.00 | 286.00 | ok | ||
| 6 | 18/01/2024 | ARTTT | SDCMN 1000A | RSTTT-04 | 2.00 | 140.00 | 280.00 | ok | ||
| 7 | 19/01/2024 | ARTTT | SDCMN 1000A | RSTTT-05 | 2.00 | 140.00 | 280.00 | ok | ||
SSERT | ||||||||||
thanks in advanced
