abdo meghari
Active Member
- Joined
- Aug 3, 2021
- Messages
- 465
- Office Version
- 2019
Hello
I search for macro to deal with at least 3000 items and cloud be 9000 rows for each sheet .
in two sheets I have data for items in columns D:F and the price in column H . so what I want when merge duplicates items based on matching columns D: E and when repeat the same item with different price should use price averages but when merge items should be separated for each sheet alone , also when merge quantity should just be for SVR sheet (means ignoring QTY totally in SR sheet when merge as I did REPORT sheet .
when merge data should be based on two cells (dates) should merge within two dates in E2,G2 . if the E2,G2 are empty then should be empty from row 5 .every time run the macro should clear data from row5 before brings the data , not repeat to the bottom the same data have already copied.
in REPORT sheet SVR header is averages prices from column H for SVR sheet ,SR header is averages prices from column H for SR sheet and in last row(TOTAL) should sum the columns I,J,K and in column L will subtract and sum for I:K as I put the formulas .
if they data are existed in SVR but not in SR should show in REPORT sheet , but if they data are existed in SR ,not in SVR should NOT show in REPORT sheet .
finally when copy data to report sheet I want keep the formatting (numbers,borders....) .
SVR sheet
SR sheet
REPORT sheet
result in REPORT sheet after write dates in E2,G2
I search for macro to deal with at least 3000 items and cloud be 9000 rows for each sheet .
in two sheets I have data for items in columns D:F and the price in column H . so what I want when merge duplicates items based on matching columns D: E and when repeat the same item with different price should use price averages but when merge items should be separated for each sheet alone , also when merge quantity should just be for SVR sheet (means ignoring QTY totally in SR sheet when merge as I did REPORT sheet .
when merge data should be based on two cells (dates) should merge within two dates in E2,G2 . if the E2,G2 are empty then should be empty from row 5 .every time run the macro should clear data from row5 before brings the data , not repeat to the bottom the same data have already copied.
in REPORT sheet SVR header is averages prices from column H for SVR sheet ,SR header is averages prices from column H for SR sheet and in last row(TOTAL) should sum the columns I,J,K and in column L will subtract and sum for I:K as I put the formulas .
if they data are existed in SVR but not in SR should show in REPORT sheet , but if they data are existed in SR ,not in SVR should NOT show in REPORT sheet .
finally when copy data to report sheet I want keep the formatting (numbers,borders....) .
SVR sheet
| TR.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | ITEM | DATE | INV.NO | BRAND | TYPE | ORIGIN | QTY | PRICE | TOTAL | ||
| 2 | 1 | 15/06/2023 | BSTR_23448 | BS 750R16 | R230 | JAP | 4.00 | 500.00 | 2,000.00 | ||
| 3 | 2 | 15/06/2023 | BSTR_23448 | BS 700R16 | R230 | JAP | 2.00 | 400.00 | 800.00 | ||
| 4 | SUM | 15/06/2023 | BSTR_23448 | 2,800.00 | |||||||
| 5 | 1 | 15/09/2023 | BSTR_23449 | GO 1200R20 | AZ0026 | CHI | 1.00 | 920.00 | 920.00 | ||
| 6 | 2 | 15/09/2023 | BSTR_23449 | GO 1200R20 | AZ0083 | CHI | 2.00 | 1,000.00 | 2,000.00 | ||
| 7 | SUM | 15/09/2023 | BSTR_23449 | 2,920.00 | |||||||
| 8 | 1 | 15/09/2023 | BSTR_23450 | BS 1200R20 | G580 | JAP | 1.00 | 1,800.00 | 1,800.00 | ||
| 9 | 2 | 15/09/2023 | BSTR_23450 | BS 1200R20 | G580 | THI | 1.00 | 1,800.00 | 1,800.00 | ||
| 10 | 3 | 15/09/2023 | BSTR_23450 | BS 1200R20 | R187 | THI | 1.00 | 1,800.00 | 1,800.00 | ||
| 11 | SUM | 15/09/2023 | BSTR_23450 | 5,400.00 | |||||||
| 12 | 1 | 20/09/2023 | BSTR_23451 | BS 750R16 | R230 | JAP | 4.00 | 450.00 | 1,800.00 | ||
| 13 | 2 | 20/09/2023 | BSTR_23451 | BS 1200R20 | R187 | THI | 2.00 | 1,650.00 | 3,300.00 | ||
| 14 | SUM | 20/09/2023 | BSTR_23451 | 5,100.00 | |||||||
| 15 | 1 | 21/09/2023 | BSTR_23452 | BS 750R16 | R230 | JAP | 2.00 | 480.00 | 960.00 | ||
| 16 | 2 | 21/09/2023 | BSTR_23452 | BS 1200R20 | R187 | THI | 3.00 | 1,550.00 | 4,650.00 | ||
| 17 | SUM | 21/09/2023 | BSTR_23452 | BS 1200R20 | G580 | THI | 4.00 | 1,900.00 | 7,600.00 | ||
| 18 | 1 | 21/09/2023 | BSTR_23453 | BS 1400R20 | VSJ | JAP | 2.00 | 2,800.00 | 5,600.00 | ||
| 19 | SUM | 21/09/2023 | BSTR_23453 | 5,600.00 | |||||||
| 20 | 1 | 21/09/2023 | BSTR_23454 | BS 1400R20 | VSJ | JAP | 2.00 | 3,000.00 | 6,000.00 | ||
| 21 | SUM | 21/09/2023 | BSTR_23454 | 6,000.00 | |||||||
SVR | |||||||||||
SR sheet
| TR.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | ITEM | DATE | INV.NO | BRAND | TYPE | ORIGIN | QTY | PRICE | TOTAL | ||
| 2 | 1 | 10/06/2023 | BSJ_23444 | BS 215/60R16 | ER30 | JAP | 4.00 | 430.00 | 1,720.00 | ||
| 3 | SUM | 10/06/2023 | BSJ_23444 | 1,720.00 | |||||||
| 4 | 1 | 15/06/2023 | BSJ_23445 | GO 1200R20 | AZ0026 | CHI | 2.00 | 955.00 | 1,910.00 | ||
| 5 | SUM | 15/06/2023 | BSJ_23445 | 1,910.00 | |||||||
| 6 | 1 | 15/09/2023 | BSJ_23446 | GO 1200R20 | AZ0026 | CHI | 2.00 | 950.00 | 1,900.00 | ||
| 7 | SUM | 15/09/2023 | BSJ_23446 | 1,900.00 | |||||||
| 8 | 1 | 15/09/2023 | BSJ_23447 | BS 1200R20 | G580 | JAP | 1.00 | 2,000.00 | 2,000.00 | ||
| 9 | 2 | 15/09/2023 | BSJ_23447 | BS 1200R20 | G580 | THI | 1.00 | 2,000.00 | 2,000.00 | ||
| 10 | 3 | 15/09/2023 | BSJ_23447 | BS 1200R20 | R187 | THI | 1.00 | 1,650.00 | 1,650.00 | ||
| 11 | SUM | 15/09/2023 | BSJ_23447 | 5,650.00 | |||||||
| 12 | 1 | 16/09/2023 | BSJ_23448 | GO 1200R20 | AZ0026 | CHI | 1.00 | 1,000.00 | 1,000.00 | ||
| 13 | SUM | 16/09/2023 | BSJ_23448 | 1,000.00 | |||||||
| 14 | 1 | 17/09/2023 | BSJ_23449 | BS 1200R20 | G580 | JAP | 2.00 | 950.00 | 1,900.00 | ||
| 15 | 2 | 17/09/2023 | BSJ_23449 | BS 1200R20 | G580 | THI | 2.00 | 990.00 | 1,980.00 | ||
| 16 | SUM | 17/09/2023 | BSJ_23449 | 3,880.00 | |||||||
| 17 | 1 | 17/09/2023 | BSJ_23450 | BS 1200R24 | G580 | JAP | 2.00 | 2,200.00 | 4,400.00 | ||
| 18 | SUM | 17/09/2023 | BSJ_23450 | 4,400.00 | |||||||
| 19 | 1 | 17/09/2023 | BSJ_23450 | BS 1200R24 | G580 | JAP | 1.00 | 1,950.00 | 1,950.00 | ||
| 20 | SUM | 17/09/2023 | BSJ_23450 | 1,950.00 | |||||||
SR | |||||||||||
REPORT sheet
| TR.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | |||
| 1 | FROM DATE | TO DATE | ||||||||
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ITEM | BRAND | TYPE | ORIGIN | QTY | SVR | SR | NET | ||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 | ||||||||||
| 11 | ||||||||||
| 12 | ||||||||||
| 13 | ||||||||||
REPORT | ||||||||||
result in REPORT sheet after write dates in E2,G2
| TR.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | |||
| 1 | FROM DATE | TO DATE | ||||||||
| 2 | 15/06/2023 | 21/09/2023 | ||||||||
| 3 | ||||||||||
| 4 | ITEM | BRAND | TYPE | ORIGIN | QTY | SVR | SR | NET | ||
| 5 | 1 | BS 700R16 | R230 | JAP | 2.00 | 400.00 | 0.00 | 800.00 | ||
| 6 | 2 | BS 750R16 | R230 | JAP | 8.00 | 475.00 | 0.00 | 3,800.00 | ||
| 7 | 3 | GO 1200R20 | AZ0026 | CHI | 1.00 | 920.00 | 969.00 | -49.00 | ||
| 8 | 4 | GO 1200R20 | AZ0083 | CHI | 2.00 | 1,000.00 | 0.00 | 2,000.00 | ||
| 9 | 5 | BS 1200R20 | G580 | JAP | 1.00 | 1,800.00 | 1,475.00 | 325.00 | ||
| 10 | 6 | BS 1200R20 | G580 | THI | 5.00 | 1,850.00 | 1,495.00 | 7,755.00 | ||
| 11 | 7 | BS 1200R20 | R187 | THI | 6.00 | 1,667.00 | 1,650.00 | 8,352.00 | ||
| 12 | 8 | BS 1400R20 | VSJ | JAP | 4.00 | 2,900.00 | 0.00 | 11,600.00 | ||
| 13 | TOTAL | 29.00 | 11,012.00 | 5,589.00 | 313,759.00 | |||||
REPORT | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L5:L13 | L5 | =I5*J5-K5 |
| I13:K13 | I13 | =SUM(I5:I12) |
