abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 682
- Office Version
- 2019
Hi,
in SR sheet I have invoices contains brands is relating with sales price and in REPORT sheet contains brands is relating with cost price . so what I want match brands in column F and price in column H for SR sheet with REPORT sheet for brands in column B and average price in column C then will create report in columns F:J will brings dates and brands and QTY and price from SR sheet (price should be smaller than price in REPORT sheet) and in BALANCE column =(I-C)*H and insert sum row to sum BALANCE column .
as to new items in SR sheet will not show in report, also if there is new brand in REPORT sheet will not show.
so just show brands are existed in both sheets based on smaller price in SR sheet than price in REPORT sheet.
every time should delete data in F:J before brings data .
the data could be 10000 rows for SR sheet and 5000 rows for range A:C in REPORT sheet.
result should be
in SR sheet I have invoices contains brands is relating with sales price and in REPORT sheet contains brands is relating with cost price . so what I want match brands in column F and price in column H for SR sheet with REPORT sheet for brands in column B and average price in column C then will create report in columns F:J will brings dates and brands and QTY and price from SR sheet (price should be smaller than price in REPORT sheet) and in BALANCE column =(I-C)*H and insert sum row to sum BALANCE column .
as to new items in SR sheet will not show in report, also if there is new brand in REPORT sheet will not show.
so just show brands are existed in both sheets based on smaller price in SR sheet than price in REPORT sheet.
every time should delete data in F:J before brings data .
the data could be 10000 rows for SR sheet and 5000 rows for range A:C in REPORT sheet.
(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 | BSJ_23444 | OUTSANDING | BS 750R16 R230 JAP | 4.00 | 510.00 | 2,040.00 | ||
3 | 2 | 15/06/2023 | CCF-1000 | BSJ_23444 | OUTSANDING | BS 1200R20 G580 JAP | 4.00 | 2,000.00 | 8,000.00 | ||
4 | SUM | 10,040.00 | |||||||||
5 | 1 | 16/06/2023 | CCF-1001 | BSJ_23445 | OUTSANDING | BS 750R16 R230 JAP | 2.00 | 470.00 | 940.00 | ||
6 | SUM | 940.00 | |||||||||
7 | 1 | 16/06/2023 | CCF-1002 | BSJ_23446 | PAID | GO 1200R20 AZ0026 CHI | 2.00 | 850.00 | 1,700.00 | ||
8 | 2 | 16/06/2023 | CCF-1002 | BSJ_23446 | PAID | BS 1200R20 G580 JAP | 2.00 | 1,900.00 | 3,800.00 | ||
9 | SUM | 5,500.00 | |||||||||
10 | 1 | 17/09/2023 | CCF-1003 | BSJ_23447 | PAID | BS 1200R20 G580 JAP | 1.00 | 1,750.00 | 1,750.00 | ||
11 | 2 | 17/09/2023 | CCF-1003 | BSJ_23447 | PAID | BS 700R16 R230 JAP | 2.00 | 410.00 | 820.00 | ||
12 | SUM | 2,570.00 | |||||||||
13 | 1 | 18/09/2023 | CCF-1004 | BSJ_23448 | PAID | BS 1200R20 G580 JAP | 1.00 | 1,990.00 | 1,990.00 | ||
14 | 2 | 18/09/2023 | CCF-1004 | BSJ_23448 | PAID | BS 700R16 R230 JAP | 2.00 | 540.00 | 1,080.00 | ||
15 | SUM | 3,070.00 | |||||||||
16 | 1 | 18/09/2023 | CCF-1005 | BSJ_23449 | PAID | BS 215/70R15C R623 THI | 1.00 | 440.00 | 440.00 | ||
17 | 2 | 18/09/2023 | CCF-1005 | BSJ_23449 | PAID | BS225/70R15C R623 THI | 1.00 | 650.00 | 650.00 | ||
18 | SUM | 1,090.00 | |||||||||
SR |
(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,005.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 |
AFTER (2).xlsm | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
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,005.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 |
result should be
(2).xlsm | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ITEM | BRAND | PRICE AVERAGE | DATE | BRAND | QTY | PRICE | BALANCE | ||||||
2 | 1 | BS 700R16 R230 JAP | 423.33 | 15/09/2023 | BS 700R16 R230 JAP | 2.00 | 410.00 | -26.66 | ||||||
3 | 2 | BS 750R16 R230 JAP | 493.33 | 16/09/2023 | BS 750R16 R230 JAP | 2.00 | 470.00 | -46.67 | ||||||
4 | 3 | GO 1200R20 AZ0026 CHI | 920.00 | 16/09/2023 | BS 1200R20 G580 JAP | 2.00 | 1,900.00 | -45.00 | ||||||
5 | 4 | GO 1200R20 AZ0083 CHI | 1,005.00 | 17/09/2023 | BS 1200R20 G580 JAP | 1.00 | 1,750.00 | -172.50 | ||||||
6 | 5 | BS 1200R20 G580 JAP | 1,922.50 | SUM | -290.83 | |||||||||
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 | |||||||||||
REPORT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J2:J3 | J2 | =(I2-C2)*H2 |
J4 | J4 | =(I4-C6)*H4 |
J5 | J5 | =(I5-C6)*H5 |
J6 | J6 | =SUM(J2:J5) |