hello
I would code match based on column B into SH2,3 with sheet MAIN if they're matched then should arrange data into column C for SH1,2 based on sheet MAIN
should show the items is corrected in column D and the item is wrong in column E somtimes the error can be in first or middile or last item is wrong and somtimes there is missed items.
the missed items as in sheet result1 in last row . the item DM in sh2 is not existed but when arrange should be add in column D and not show in column E into sheet result1
as to missed items as in sheet result1 in first last row . the item DDTR in sh3 is not existed but when arrange should not show in column D and should show in column E into sheet result2, also if the wrong item doesn't contain space as in sh2,3 but it contains space in sheet MAIN then should consider error for the whole item which contain wrong space as in second row in sheet result2. the result should be in column D,E after arrange data in column C into sheet SH2,3 based on the macro
I hop what I ask for it . can be possible or logic espically if my result is not clear in sheets result1,2 . if it's not please guide me what the best way to do that .
thanks
I would code match based on column B into SH2,3 with sheet MAIN if they're matched then should arrange data into column C for SH1,2 based on sheet MAIN
should show the items is corrected in column D and the item is wrong in column E somtimes the error can be in first or middile or last item is wrong and somtimes there is missed items.
the missed items as in sheet result1 in last row . the item DM in sh2 is not existed but when arrange should be add in column D and not show in column E into sheet result1
as to missed items as in sheet result1 in first last row . the item DDTR in sh3 is not existed but when arrange should not show in column D and should show in column E into sheet result2, also if the wrong item doesn't contain space as in sh2,3 but it contains space in sheet MAIN then should consider error for the whole item which contain wrong space as in second row in sheet result2. the result should be in column D,E after arrange data in column C into sheet SH2,3 based on the macro
| missed (1).xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CODE | TTR | MMR | NNR | QTY | PRICE | TOTAL | ||
| 2 | 07/08/2021 | SLFR-100 | FOOD-1 | PR100 | NRIT1 | 10 | 25 | 250 | ||
| 3 | 08/08/2021 | SLFR-101 | FOOD-2 | PR101 | NRIT2 | 20 | 30 | 600 | ||
| 4 | 09/08/2021 | SLFR-102 | FOOD-3 | PR102 | NRIT3 | 23 | 35 | 805 | ||
| 5 | 10/08/2021 | SLFR-103 | FOOD-4 | PR103 | NRIT4 | 25 | 30 | 750 | ||
| 6 | 11/08/2021 | SLFR-104 | FOOD-5 | PR104 | NRIT5 | 20 | 40 | 800 | ||
| 7 | 12/08/2021 | BT | FOOD-6 | PR105 | NRIT6 | 10 | 45 | 450 | ||
| 8 | 13/08/2021 | BRR | FOOD-7 | PR106 | NRIT7 | 20 | 45 | 900 | ||
| 9 | 14/08/2021 | SLFR-107 | FF/100 | PR107 | NRIT8 | 15 | 55 | 825 | ||
| 10 | 15/08/2021 | DDTR | FF/100L44 | PR108 | NRIT9 | 5 | 60 | 300 | ||
| 11 | TOT.MONTH | 148 | 365 | 5680 | ||||||
| 12 | 01/09/2021 | DM | 120**225l20 FSLUNCH | PR121 | NRIT22 | 10 | 20 | 200 | ||
| 13 | 02/09/2021 | SLFR-121 | 2000-15 55T N125 FDO | PR122 | NRIT23 | 5 | 20 | 100 | ||
| 14 | 03/09/2021 | VBA123 | 25.5*S12.5 BOTTOLE 1L | PR123 | NRIT24 | 20 | 25 | 500 | ||
| 15 | TOT.MONTH | 35 | 65 | 800 | ||||||
MAIN | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F11:H11 | F11 | =SUM(F2:F10) |
| H12:H14,H2:H10 | H2 | =F2*G2 |
| F15:H15 | F15 | =SUM(F12:F14) |
| missed (1).xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | SSTR | QTY | PRICE | TOTAL | ||
| 2 | 1 | VBA123 | 123 215.5*S12.5BOTTOLE 1L PR123 NRIT214 | 23 | 35 | 805 | ||
| 3 | 2 | SLFR-101 | FOOD-12 PR1011 NRIT2K | 5 | 60 | 300 | ||
| 4 | 3 | SLFR-100 | FOOOD-1 RF100 NBGFR | 10 | 25 | 250 | ||
| 5 | 4 | DM | 120***225l20 FSLUNCH PR121 | |||||
SH2 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F4 | F2 | =D2*E2 |
| missed (1).xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | SSTR | QTY | PRICE | TOTAL | ||
| 2 | 1 | DDTR | FF/100L44PR108 NRIT9 JK/100 | 23 | 35 | 805 | ||
| 3 | 2 | SLFR-121 | 2000-16 55TN125 FDO | 5 | 60 | 300 | ||
| 4 | 3 | BRR | FOOD-1111 NRIT8 | 10 | 25 | 250 | ||
| 5 | 4 | SLFR-107 | FF/100 P107 NRIT8 | 10 | 25 | 250 | ||
SH3 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F5 | F2 | =D2*E2 |
| missed (1).xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | |||
| 1 | SSTR | RIGHT | WRONG | QTY | PRICE | TOTAL | ||
| 2 | 25.5*S12.5 BOTTOLE 1LPR123 NRIT24 | 25.5*S12.5 BOTTOLE NRIT24 | 123 215.5*S12.5BOTTOLE NRIT214 | 23 | 35 | 805 | ||
| 3 | FOOD-2 PR101 NRIT2 | FOOD-2 PR101 NRIT2 | FOOD-12 PR1011 NRIT2K | 5 | 60 | 300 | ||
| 4 | FOOD-1 PR100 NRIT1 | FOOD-1 PR100 NRIT1 | FOOOD-1 RF100 NBGFR | 10 | 25 | 250 | ||
| 5 | 120**225l20 FSLUNCH PR121 NRIT22 | 120**225l20 NRIT22 | 120***225l20 | 10 | 20 | 200 | ||
RESULT1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H5 | H2 | =F2*G2 |
| missed (1).xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ITEM | CODE | SSTR | RIGHT | WRONG | QTY | PRICE | TOTAL | ||
| 2 | 1 | DDTR | FF/100L44 PR108 NRIT9 | JK/100 | 23 | 35 | 805 | |||
| 3 | 2 | SLFR-121 | 2000-15 55T N125 FDO PR122 NRIT23 | 2000-15 55T N125 FDO PR122 NRIT23 | 2000-16 55TN125 | 5 | 60 | 300 | ||
| 4 | 3 | BRR | FOOD-7 PR106 NRIT24 | FOOD-7 PR106 NRIT24 | FOOD-1111 NRIT8 | 10 | 25 | 250 | ||
| 5 | 4 | SLFR-107 | FF/100 PR107 NRIT8 | PR107 | P107 | 10 | 25 | 250 | ||
RESULT2 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H5 | H2 | =F2*G2 |
I hop what I ask for it . can be possible or logic espically if my result is not clear in sheets result1,2 . if it's not please guide me what the best way to do that .
thanks
