Hello
I need to help from experts master to writing smart code to deal with 15000 rows for each sheet .
if cells D2,E2 are empty then will create report from row 5 in expected result
first step will merge QTY for each sheet alone based on two columns together (B,D)
and the headers G:J are matched with sheets names to merge QTY for each sheet alone .
second step will calculate PURCHASE AVERAGE based on average price for just PURCHASE sheet , in column I , will calculate SALES AVERAGE based on average price for just SALES sheet in column I , but when calculate price average should be based on just column B .
and calculation as the formula is existed in BALANCE column.
if D2,E2 are empty
should be
if D2,E2 contains dates then should merge within dates like this
thanks in advance
I need to help from experts master to writing smart code to deal with 15000 rows for each sheet .
if cells D2,E2 are empty then will create report from row 5 in expected result
first step will merge QTY for each sheet alone based on two columns together (B,D)
and the headers G:J are matched with sheets names to merge QTY for each sheet alone .
second step will calculate PURCHASE AVERAGE based on average price for just PURCHASE sheet , in column I , will calculate SALES AVERAGE based on average price for just SALES sheet in column I , but when calculate price average should be based on just column B .
and calculation as the formula is existed in BALANCE column.
| 2222.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DATE | BBT | INV NO | NAME | cmm | ddm | nnm | QTY | PRICE | TOTAL | ||
| 2 | 01/01/2021 | bfg-hy1 | invn-as1 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 200.00 | $350.00 | $70,000.00 | ||
| 3 | 02/01/2021 | bfg-hy2 | invn-as2 | asz-2 | cmm-1001 | ddm-1001 | nnm-1001 | 65.00 | $600.00 | $39,000.00 | ||
| 4 | 03/01/2021 | bfg-hy3 | invn-as3 | asz-3 | cmm-1002 | ddm-1002 | nnm-1002 | 80.00 | $700.00 | $56,000.00 | ||
| 5 | 04/01/2021 | bfg-hy4 | invn-as4 | asz-4 | cmm-1003 | ddm-1003 | nnm-1003 | 54.00 | $500.00 | $27,000.00 | ||
| 6 | 05/01/2021 | bfg-hy5 | invn-as5 | asz-5 | cmm-1004 | ddm-1004 | nnm-1004 | 34.00 | $400.00 | $13,600.00 | ||
| 7 | 06/01/2021 | bfg-hy6 | invn-as6 | asz-6 | cmm-1005 | ddm-1005 | nnm-1005 | 12.00 | $200.00 | $2,400.00 | ||
| 8 | 07/01/2021 | bfg-hy1 | invn-as7 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 45.00 | $400.00 | $18,000.00 | ||
| 9 | 09/01/2021 | bfg-hy3 | invn-as9 | asz-3 | cmm-1002 | ddm-1002 | nnm-1002 | 54.00 | $900.00 | $48,600.00 | ||
| 10 | 10/01/2021 | bfg-hy4 | invn-as10 | asz-4 | cmm-1003 | ddm-1003 | nnm-1003 | 21.00 | $800.00 | $16,800.00 | ||
| 11 | 11/01/2021 | bfg-hy5 | invn-as11 | asz-5 | cmm-1004 | ddm-1004 | nnm-1004 | 55.00 | $670.00 | $36,850.00 | ||
| 12 | 12/01/2021 | bfg-hy6 | invn-as12 | asz-6 | cmm-1005 | ddm-1005 | nnm-1005 | 66.00 | $431.00 | $28,446.00 | ||
Purchase | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:J12 | J2 | =H2*I2 |
| 2222.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DATE | BBT | INV NO | NAME | cmm | ddm | nnm | QTY | PRICE | TOTAL | ||
| 2 | 01/01/2021 | bfg-hy1 | invn-as1 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $400.00 | $4,000.00 | ||
| 3 | 02/01/2021 | bfg-hy2 | invn-as2 | asz-2 | cmm-1001 | ddm-1001 | nnm-1001 | 5.00 | $700.00 | $3,500.00 | ||
| 4 | 03/01/2021 | bfg-hy3 | invn-as3 | asz-3 | cmm-1002 | ddm-1002 | nnm-1002 | 20.00 | $800.00 | $16,000.00 | ||
| 5 | 04/01/2021 | bfg-hy4 | invn-as4 | asz-4 | cmm-1003 | ddm-1003 | nnm-1003 | 5.00 | $800.00 | $4,000.00 | ||
| 6 | 05/01/2021 | bfg-hy5 | invn-as5 | asz-5 | cmm-1004 | ddm-1004 | nnm-1004 | 2.00 | $700.00 | $1,400.00 | ||
| 7 | 06/01/2021 | bfg-hy6 | invn-as6 | asz-6 | cmm-1005 | ddm-1005 | nnm-1005 | 1.00 | $600.00 | $600.00 | ||
| 8 | 07/01/2021 | bfg-hy1 | invn-as7 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $500.00 | $5,000.00 | ||
| 9 | 08/01/2021 | bfg-hy2 | invn-as8 | asz-2 | cmm-1001 | ddm-1001 | nnm-1001 | 22.00 | $700.00 | $15,400.00 | ||
| 10 | 09/01/2021 | bfg-hy3 | invn-as9 | asz-3 | cmm-1002 | ddm-1002 | nnm-1002 | 12.00 | $1,000.00 | $12,000.00 | ||
| 11 | 10/01/2021 | bfg-hy4 | invn-as10 | asz-4 | cmm-1003 | ddm-1003 | nnm-1003 | 10.00 | $1,000.00 | $10,000.00 | ||
| 12 | 11/01/2021 | bfg-hy5 | invn-as11 | asz-5 | cmm-1004 | ddm-1004 | nnm-1004 | 4.00 | $700.00 | $2,800.00 | ||
| 13 | 12/01/2021 | bfg-hy6 | invn-as12 | asz-6 | cmm-1005 | ddm-1005 | nnm-1005 | 2.00 | $800.00 | $1,600.00 | ||
| 14 | 12/01/2021 | bfg-hy6 | invn-as13 | asz-7 | cmm-1005 | ddm-1005 | nnm-1005 | 2.00 | $820.00 | $1,640.00 | ||
| 15 | 13/01/2021 | bfg-hy1 | invn-as14 | asz-8 | cmm-1000 | ddm-1000 | nnm-1000 | 2.00 | $420.00 | $840.00 | ||
Sales | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:J15 | J2 | =H2*I2 |
| 2222.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DATE | BBT | INV NO | NAME | cmm | ddm | nnm | QTY | PRICE | TOTAL | ||
| 2 | 01/01/2021 | bfg-hy1 | invn-as1 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $350.00 | $3,500.00 | ||
| 3 | 02/01/2021 | bfg-hy1 | invn-as2 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $400.00 | $4,000.00 | ||
| 4 | 03/01/2021 | bfg-hy1 | invn-as3 | asz-3 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $350.00 | $3,500.00 | ||
| 5 | 04/01/2021 | bfg-hy2 | invn-as4 | asz-4 | cmm-1001 | ddm-1001 | nnm-1001 | 15.00 | $600.00 | $9,000.00 | ||
| 6 | 05/01/2021 | bfg-hy3 | invn-as5 | asz-5 | cmm-1002 | ddm-1002 | nnm-1002 | 10.00 | $700.00 | $7,000.00 | ||
purchase returns | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:J6 | J2 | =H2*I2 |
| 2222.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DATE | BBT | INV NO | NAME | cmm | ddm | nnm | QTY | PRICE | TOTAL | ||
| 2 | 01/01/2021 | bfg-hy1 | invn-as1 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 10.00 | $400.00 | $4,000.00 | ||
| 3 | 02/01/2021 | bfg-hy2 | invn-as2 | asz-2 | cmm-1001 | ddm-1001 | nnm-1001 | 5.00 | $700.00 | $3,500.00 | ||
| 4 | 03/01/2021 | bfg-hy2 | invn-as3 | asz-3 | cmm-1001 | ddm-1001 | nnm-1001 | 5.00 | $700.00 | $3,500.00 | ||
| 5 | 04/01/2021 | bfg-hy1 | invn-as4 | asz-1 | cmm-1000 | ddm-1000 | nnm-1000 | 5.00 | $500.00 | $2,500.00 | ||
| 6 | 05/01/2021 | bfg-hy2 | invn-as5 | asz-3 | cmm-1001 | ddm-1001 | nnm-1001 | 2.00 | $700.00 | $1,400.00 | ||
sales returns | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:J6 | J2 | =H2*I2 |
if D2,E2 are empty
| 2222.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | FROM DATE | TO DATE | |||||||||||||
| 2 | |||||||||||||||
| 3 | |||||||||||||||
| 4 | ITEM | BBT | NAME | cmm | ddm | nnm | Purchase | Sales | purchase returns | sales returns | PURCHASE AVERAGE | SALE AVERAGE | BALANCE | ||
| 5 | |||||||||||||||
| 6 | |||||||||||||||
| 7 | |||||||||||||||
| 8 | |||||||||||||||
expected result | |||||||||||||||
should be
| 2222.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | FROM DATE | TO DATE | |||||||||||||
| 2 | |||||||||||||||
| 3 | |||||||||||||||
| 4 | ITEM | BBT | NAME | cmm | ddm | nnm | Purchase | Sales | purchase returns | sales returns | PURCHASE AVERAGE | SALE AVERAGE | BALANCE | ||
| 5 | 1 | bfg-hy1 | asz-1 | cmm-1000 | cmm-1000 | ddm-1000 | 245.00 | 20.00 | 20.00 | 15.00 | $375.00 | $440.00 | $14,300.00 | ||
| 6 | 2 | bfg-hy1 | asz-3 | cmm-1000 | cmm-1000 | ddm-1000 | - | - | 10.00 | - | $375.00 | $440.00 | -$650.00 | ||
| 7 | 3 | bfg-hy1 | asz-8 | cmm-1000 | cmm-1000 | ddm-1000 | - | 2.00 | - | - | $375.00 | $440.00 | -$130.00 | ||
| 8 | 4 | bfg-hy2 | asz-2 | cmm-1001 | cmm-1001 | ddm-1001 | 65.00 | 27.00 | - | 5.00 | $600.00 | $700.00 | $4,300.00 | ||
| 9 | 5 | bfg-hy2 | asz-3 | cmm-1001 | cmm-1001 | ddm-1001 | - | - | - | 7.00 | $600.00 | $700.00 | $700.00 | ||
| 10 | 6 | bfg-hy2 | asz-4 | cmm-1001 | cmm-1001 | ddm-1001 | - | - | 15.00 | - | $600.00 | $700.00 | -$1,500.00 | ||
| 11 | 7 | bfg-hy3 | asz-3 | cmm-1002 | cmm-1002 | ddm-1002 | 134.00 | 32.00 | - | - | $700.00 | $900.00 | $20,400.00 | ||
| 12 | 8 | bfg-hy3 | asz-5 | cmm-1002 | ddm-1002 | nnm-1002 | - | 10.00 | - | $700.00 | $900.00 | -$2,000.00 | |||
| 13 | 9 | bfg-hy4 | asz-4 | cmm-1003 | cmm-1003 | ddm-1003 | 75.00 | 15.00 | - | - | $500.00 | $900.00 | $24,000.00 | ||
| 14 | 10 | bfg-hy5 | asz-5 | cmm-1004 | cmm-1004 | ddm-1004 | 89.00 | 6.00 | - | - | $670.00 | $700.00 | $2,490.00 | ||
| 15 | 11 | bfg-hy6 | asz-6 | cmm-1005 | ddm-1005 | nnm-1005 | 78.00 | 3.00 | - | - | $315.50 | $740.00 | $31,837.50 | ||
| 16 | 12 | bfg-hy6 | asz-7 | cmm-1005 | ddm-1005 | nnm-1005 | - | 2.00 | - | - | $315.50 | $740.00 | -$849.00 | ||
expected result | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| M5:M16 | M5 | =(G5+J5-I5-H5) *(L5-K5) |
if D2,E2 contains dates then should merge within dates like this
| 2222.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | FROM DATE | TO DATE | |||||||||||||
| 2 | 01/01/2021 | 04/01/2021 | |||||||||||||
| 3 | |||||||||||||||
| 4 | ITEM | BBT | NAME | cmm | ddm | nnm | Purchase | Sales | purchase returns | sales returns | PURCHASE AVERAGE | SALE AVERAGE | BALANCE | ||
| 5 | 1 | bfg-hy1 | asz-1 | cmm-1000 | cmm-1000 | ddm-1000 | 200.00 | 10.00 | 20.00 | 15.00 | $350.00 | $400.00 | $9,250.00 | ||
| 6 | 2 | bfg-hy1 | asz-3 | cmm-1000 | cmm-1000 | ddm-1000 | - | - | 10.00 | - | $350.00 | $400.00 | -$500.00 | ||
| 7 | 3 | bfg-hy2 | asz-2 | cmm-1001 | cmm-1001 | ddm-1001 | 65.00 | 5.00 | - | 5.00 | $600.00 | $700.00 | $6,500.00 | ||
| 8 | 4 | bfg-hy2 | asz-3 | cmm-1001 | cmm-1001 | ddm-1001 | - | - | - | 5.00 | $600.00 | $700.00 | $500.00 | ||
| 9 | 5 | bfg-hy2 | asz-4 | cmm-1001 | cmm-1001 | ddm-1001 | - | - | 15.00 | - | $600.00 | $700.00 | -$1,500.00 | ||
| 10 | 6 | bfg-hy3 | asz-3 | cmm-1002 | cmm-1002 | ddm-1002 | 80.00 | 20.00 | - | - | $700.00 | $800.00 | $6,000.00 | ||
| 11 | 7 | bfg-hy4 | asz-4 | cmm-1003 | cmm-1003 | ddm-1003 | 54.00 | 5.00 | - | - | $500.00 | $800.00 | $14,700.00 | ||
expected result | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| M5:M11 | M5 | =(G5+J5-I5-H5) *(L5-K5) |
thanks in advance
Last edited:
