abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 682
- Office Version
- 2019
Hi,
I would macro to merge price in columns D, H as average price for each brand in columns B,F for SV,STOCK sheets.
I don't need power query at all .
the result should be in REPORT sheet
so every new brand in two sheets will be show , the data in both sheets could be 10000 rows
I would macro to merge price in columns D, H as average price for each brand in columns B,F for SV,STOCK sheets.
I don't need power query at all .
AFTER (2).xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | ITEM | DATE | CUSTOMERS | INV.NO | CASE | BRAND | QTY | PRICE | BALANCE | ||
2 | 1 | 15/06/2023 | CCF-1000 | BSTR_23448 | OUTSANDING | BS 750R16 R230 JAP | 3.00 | 500.00 | 1,500.00 | ||
3 | 2 | 15/06/2023 | CCF-1000 | BSTR_23448 | OUTSANDING | BS 700R16 R230 JAP | 2.00 | 400.00 | 800.00 | ||
4 | SUM | 2,300.00 | |||||||||
5 | 1 | 15/09/2023 | CCF-1000 | BSTR_23449 | OUTSANDING | GO 1200R20 AZ0026 CHI | 1.00 | 920.00 | 920.00 | ||
6 | 2 | 15/09/2023 | CCF-1000 | BSTR_23449 | OUTSANDING | GO 1200R20 AZ0083 CHI | 1.00 | 1,000.00 | 1,000.00 | ||
7 | SUM | 1,920.00 | |||||||||
8 | 1 | 15/09/2023 | CCF-1000 | BSTR_23450 | PAID | BS 1200R20 G580 JAP | 9.00 | 1,800.00 | 16,200.00 | ||
9 | 2 | 15/09/2023 | CCF-1000 | BSTR_23450 | PAID | BS 1200R20 G580 THI | 9.00 | 1,800.00 | 16,200.00 | ||
10 | 3 | 15/09/2023 | CCF-1000 | BSTR_23450 | PAID | BS 1200R20 R187 THI | 10.00 | 1,800.00 | 18,000.00 | ||
11 | SUM | 50,400.00 | |||||||||
12 | 1 | 16/09/2023 | CCF-1001 | BSTR_23452 | PAID | BS 1200R20 G580 JAP | 4.00 | 2,000.00 | 7,200.00 | ||
13 | SUM | 7,200.00 | |||||||||
14 | 1 | 17/09/2023 | CCF-1001 | BSTR_23453 | OUTSANDING | BS 1200R20 G580 JAP | 3.00 | 1,990.00 | 5,970.00 | ||
15 | 2 | 17/09/2023 | CCF-1001 | BSTR_23453 | OUTSANDING | BS 750R16 R230 JAP | 2.00 | 490.00 | 980.00 | ||
16 | 3 | 17/09/2023 | CCF-1001 | BSTR_23453 | OUTSANDING | BS 700R16 R230 JAP | 2.00 | 440.00 | 880.00 | ||
17 | 4 | 17/09/2023 | CCF-1001 | BSTR_23453 | OUTSANDING | BS 1200R20 R187 THI | 3.00 | 1,770.00 | 5,310.00 | ||
18 | SUM | 13,140.00 | |||||||||
19 | 1 | 16/09/2023 | CCF-1001 | BSTR_23454 | PAID | BS 205/70R15C R623 JAP | 4.00 | 520.00 | 2,080.00 | ||
20 | SUM | 2,080.00 | |||||||||
SV |
AFTER (2).xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | BRAND | QTY | UNIT PRICE | BALANCE | ||
2 | 1 | GO 1200R20 AZ0026 CHI | 10 | 980 | 9,800.00 | ||
3 | 2 | GO 1200R20 AZ0083 CHI | 200 | 1010 | 202,000.00 | ||
4 | 3 | BS 1200R20 G580 JAP | 10 | 1900 | 19,000.00 | ||
5 | 4 | BS 1200R20 G580 THI | 20 | 1800 | 36,000.00 | ||
6 | 5 | BS 1200R20 R187 THI | 10 | 1790 | 17,900.00 | ||
7 | 6 | BS 1400R20 VSJ JAP | 10 | 3200 | 32,000.00 | ||
8 | 7 | BS 1200R24 G580 JAP | 10 | 2200 | 22,000.00 | ||
9 | 8 | BS 700R16 R230 JAP | 200 | 430 | 86,000.00 | ||
10 | 9 | BS 750R16 R230 JAP | 100 | 490 | 49,000.00 | ||
STOCK |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E10 | E2 | =C2*D2 |
the result should be in REPORT sheet
AFTER (2).xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | ITEM | BRAND | PRICE AVERAGE | ||
2 | 1 | BS 700R16 R230 JAP | 423.33 | ||
3 | 2 | BS 750R16 R230 JAP | 493.33 | ||
4 | 3 | GO 1200R20 AZ0026 CHI | 920.00 | ||
5 | 4 | GO 1200R20 AZ0083 CHI | 1,000.00 | ||
6 | 5 | BS 1200R20 G580 JAP | 1,922.50 | ||
7 | 6 | BS 1200R20 G580 THI | 1,800.00 | ||
8 | 7 | BS 1200R20 R187 THI | 1,786.67 | ||
9 | 8 | BS 1200R24 G580 JAP | 2,200.00 | ||
10 | 9 | BS 1400R20 VSJ JAP | 3,200.00 | ||
11 | 10 | BS 205/70R15C R623 JAP | 520.00 | ||
REPORT |
so every new brand in two sheets will be show , the data in both sheets could be 10000 rows