hello
I search for macro to transpose data from multiple sheets ( BUYING,SELLING,RETSEL,RETBUY) to sheet RESULT . I put the expected result in sheet RESULT
NOTE : 1- if there is duplicate item should sum the repeated item based on column B for COL F (QTY)
2- the headers about ( BUYING,SELLING,RETSEL,RETBUY) in sheet RESULT brings the QTY based on sheets names(( BUYING,SELLING,RETSEL,RETBUY)
3- about the BALANCE ,COSTING,SELLENG I put the formula how should calculate , but shouldn't show the formulas .
4- about COST AVERAGE PRICE should calculate price average from sheet BUYING because sometimes there items change the price and the same thing about SALE AVERAGE PRICE should calculate price average from sheet SELLING .
5- if there cells are empty should be mark "-" into cells
6- my real data for each sheet about 1200 rows at least and increase every time
finally if it's possible create the whole data in sheet RESULT with headers and borders and formatting with considering every time run the macro should replace data.
thanks in advance
I search for macro to transpose data from multiple sheets ( BUYING,SELLING,RETSEL,RETBUY) to sheet RESULT . I put the expected result in sheet RESULT
NOTE : 1- if there is duplicate item should sum the repeated item based on column B for COL F (QTY)
2- the headers about ( BUYING,SELLING,RETSEL,RETBUY) in sheet RESULT brings the QTY based on sheets names(( BUYING,SELLING,RETSEL,RETBUY)
3- about the BALANCE ,COSTING,SELLENG I put the formula how should calculate , but shouldn't show the formulas .
4- about COST AVERAGE PRICE should calculate price average from sheet BUYING because sometimes there items change the price and the same thing about SALE AVERAGE PRICE should calculate price average from sheet SELLING .
5- if there cells are empty should be mark "-" into cells
6- my real data for each sheet about 1200 rows at least and increase every time
finally if it's possible create the whole data in sheet RESULT with headers and borders and formatting with considering every time run the macro should replace data.
| TR.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CODE | GOODS | TYPE | PR | QTY | UNIT PRICE | TOTAL | ||
| 2 | 01/01/2021 | FR-00 | BANANA | FO | PL | 11.00 | 12.00 | 132.00 | ||
| 3 | 02/01/2021 | FR-01 | APPLE | FR | PPL | 10.00 | 14.00 | 140.00 | ||
| 4 | 03/01/2021 | FR-02 | PEACH | FR | LLP | 20.00 | 15.00 | 300.00 | ||
| 5 | 04/01/2021 | FR-03 | PEAR | FR | TTL | 30.00 | 12.00 | 360.00 | ||
| 6 | 05/01/2021 | FR-04 | STRWBERRAY | FR | MML | 25.00 | 20.00 | 500.00 | ||
| 7 | 06/01/2021 | FR-05 | GRAPES | FR | TTL | 11.00 | 25.00 | 275.00 | ||
| 8 | 07/01/2021 | FR-00 | BANANA | FO | PL | 11.00 | 12.00 | 132.00 | ||
BUYING | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H8 | H2 | =F2*G2 |
| TR.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CODE | GOODS | TYPE | PR | QTY | UNIT PRICE | TOTAL | ||
| 2 | 01/02/2021 | FR-00 | BANANA | FO | PL | 5.00 | 15.00 | 75.00 | ||
| 3 | 02/02/2021 | FR-01 | APPLE | FR | PPL | 2.00 | 16.00 | 32.00 | ||
| 4 | 03/02/2021 | FR-02 | PEACH | FR | LLP | 3.00 | 16.00 | 48.00 | ||
| 5 | 04/02/2021 | FR-03 | PEAR | FR | TTL | 2.00 | 14.00 | 28.00 | ||
| 6 | 05/02/2021 | FR-04 | STRWBERRAY | FR | MML | 10.00 | 22.00 | 220.00 | ||
| 7 | 06/02/2021 | FR-01 | APPLE | FR | PPL | 2.00 | 15.00 | 30.00 | ||
| 8 | 07/02/2021 | FR-02 | PEACH | FR | LLP | 3.00 | 13.00 | 39.00 | ||
SELLING | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H8 | H2 | =F2*G2 |
| TR.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CODE | GOODS | TYPE | PR | QTY | UNIT PRICE | TOTAL | ||
| 2 | 01/03/2021 | FR-00 | BANANA | FO | PL | 5.00 | 15.00 | 75.00 | ||
| 3 | 02/03/2021 | FR-01 | APPLE | FR | PPL | 2.00 | 16.00 | 32.00 | ||
| 4 | 03/03/2021 | FR-02 | PEACH | FR | LLP | 3.00 | 16.00 | 48.00 | ||
| 5 | 04/03/2021 | FR-03 | PEAR | FR | TTL | 2.00 | 14.00 | 28.00 | ||
| 6 | 06/03/2021 | FR-03 | PEAR | FR | TTL | 1.00 | 14.00 | 14.00 | ||
RETSEL | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H6 | H2 | =F2*G2 |
| TR.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | DATE | CODE | GOODS | TYPE | PR | QTY | UNIT PRICE | TOTAL | ||
| 2 | 15/03/2021 | FR-00 | BANANA | FO | PL | 5.00 | 15.00 | 75.00 | ||
| 3 | 16/03/2021 | FR-01 | APPLE | FR | PPL | 2.00 | 16.00 | 32.00 | ||
| 4 | 17/03/2021 | FR-01 | APPLE | FR | PPL | 1.00 | 16.00 | 16.00 | ||
RETBUY | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H4 | H2 | =F2*G2 |
| TR.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | ITEM | 1 | 2 | 3 | 4 | 5 | 6 | ||
| 2 | CODE | FR-00 | FR-01 | FR-02 | FR-03 | FR-04 | FR-05 | ||
| 3 | BUYING | 22.00 | 10.00 | 20.00 | 30.00 | 25.00 | 11.00 | ||
| 4 | SELLING | 5.00 | 4.00 | 6.00 | 2.00 | 10.00 | - | ||
| 5 | RETSEL | 5.00 | 2.00 | 3.00 | 3.00 | - | - | ||
| 6 | RETBUY | 5.00 | 3.00 | - | - | - | - | ||
| 7 | BALANCE | 17.00 | 5.00 | 17.00 | 31.00 | 15.00 | 11.00 | ||
| 8 | COST AVERAGE PRICE | 13.00 | 14.00 | 15.00 | 12.00 | 20.00 | 25.00 | ||
| 9 | SALE AVERAGE PRICE | 15.00 | 15.50 | 14.50 | 14.00 | 22.00 | - | ||
| 10 | COSTING | 221.00 | 70.00 | 255.00 | 372.00 | 300.00 | 275.00 | ||
| 11 | SELLING | 255.00 | 77.50 | 246.50 | 434.00 | 330.00 | - | ||
RESULT | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B7:G7 | B7 | =B3-B4+B5-B6 |
| B10:G10 | B10 | =B8*B7 |
| B11:G11 | B11 | =B9*B7 |
thanks in advance
