Good day,
I have once sheet that has a lot of data of sales and commissions, etc.
The data is complied by use of remittances that is received daily and is dependent on when an items is sold.
Due to this it means that most of the the information for an item received in June might only be received August as it was only sold in august and then another in September, etc.
due to this what is needed every time new data of items sold gets added onto a sheet, the data needs to be sorted by the received date, then copied over as a whole to each separate sheet for each separate month, and then the sorting reverted back to date of sale for the combined data.
I would like to know if there is a way where a can create a formula that will extract the data from the combined sheet (where I enter the data as the items are sold), then the formula entered on the January sheet will only extract the data where the item was received in January and only display that data, then the same for February on its own sheet, etc.
I tried putting the data in a pivot table and then using vlookup and index/match, etc but i am not coming right.
Below is an example of dummy data as it will be on the combined sheet sorted by the received date. (hope the format is readable)
As it will be noticed, if sales reports is received for items sold and those items was received in march, april and may, i will have to enter the data on the combined sheet sort it first then copy those three months as a whole over again.
as the sales can rack up to over 10000 sales a month and usually comes in 4 or 5 times a week. entering the data almost daily and then sorting and copy and pasting becomes a tedious process and wastes a lot of time.
Thank you,
<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
I have once sheet that has a lot of data of sales and commissions, etc.
The data is complied by use of remittances that is received daily and is dependent on when an items is sold.
Due to this it means that most of the the information for an item received in June might only be received August as it was only sold in august and then another in September, etc.
due to this what is needed every time new data of items sold gets added onto a sheet, the data needs to be sorted by the received date, then copied over as a whole to each separate sheet for each separate month, and then the sorting reverted back to date of sale for the combined data.
I would like to know if there is a way where a can create a formula that will extract the data from the combined sheet (where I enter the data as the items are sold), then the formula entered on the January sheet will only extract the data where the item was received in January and only display that data, then the same for February on its own sheet, etc.
I tried putting the data in a pivot table and then using vlookup and index/match, etc but i am not coming right.
Below is an example of dummy data as it will be on the combined sheet sorted by the received date. (hope the format is readable)
As it will be noticed, if sales reports is received for items sold and those items was received in march, april and may, i will have to enter the data on the combined sheet sort it first then copy those three months as a whole over again.
as the sales can rack up to over 10000 sales a month and usually comes in 4 or 5 times a week. entering the data almost daily and then sorting and copy and pasting becomes a tedious process and wastes a lot of time.
Thank you,
| Reference | Remittance no | Received Date | LotNo | Description | Grn | Qnty | Sell Price | Total | Vat | Seller Com 30% | Com Vat | Profit | Sell date |
| 823424 | 60643 | 11-Mar-17 | 140 | TOMTOM GPS With Cable | 890466 | 1 | R50.00 | R50.00 | R0.00 | R15.00 | R2.10 | R32.90 | 09-Jun-17 |
| 826801 | 60624 | 11-Mar-17 | 89 | Samsung 40" LED TV (No Remote) | 890465 | 1 | R800.00 | R800.00 | R0.00 | R240.00 | R33.60 | R526.40 | 25-Jul-17 |
| 805725 | 61095 | 08-Apr-17 | 200 | Samsung 13 KG Top Loader | 890528 | 1 | R450.00 | R450.00 | R0.00 | R135.00 | R18.90 | R296.10 | 05-Jun-17 |
| 805725 | 61095 | 08-Apr-17 | 224 | LOT: Digital calipre & Brite L | 890528 | 1 | R160.00 | R160.00 | R0.00 | R48.00 | R6.72 | R105.28 | 05-Jun-17 |
| 805725 | 61095 | 08-Apr-17 | 310 | Whirlpool Silver Dishwasher | 890528 | 1 | R200.00 | R200.00 | R0.00 | R60.00 | R7.40 | R131.60 | 05-Jun-17 |
| 805725 | 61095 | 08-Apr-17 | 365 | Bosch Stainless Steel Front 35 | 890528 | 1 | R2 000.00 | R2 000.00 | R0.00 | R600.00 | R84.00 | R1 316.00 | 05-Jun-17 |
| 805725 | 61095 | 08-Apr-17 | 400 | LOT 2 Sewing Machines | 890528 | 1 | R500.00 | R500.00 | R0.00 | R150.00 | R21.00 | R329.00 | 09-Jun-17 |
| 834064 | 61125 | 08-Apr-17 | 360 | HiSense 50" LED TV With Remote | 890523 | 1 | R250.00 | R250.00 | R0.00 | R750.00 | R10.50 | R164.50 | 25-Jul-17 |
| 805725 | 61241 | 22-Apr-17 | 85 | DEFY Autodry Silver Tumble | 890528 | 1 | R200.00 | R200.00 | R0.00 | R60.00 | R8.40 | R131.60 | 09-Jun-17 |
| 805725 | 61241 | 22-Apr-17 | 99 | Genises Hydrovac Vacuum | 890528 | 1 | R180.00 | R180.00 | R0.00 | R54.00 | R7.56 | R118.44 | 09-Jun-17 |
| 834021 | 61242 | 22-Apr-17 | 93 | Samsung 42" TV With Remote | 890542 | 1 | R350.00 | R350.00 | R0.00 | R105.00 | R14.70 | R230.30 | 25-Jul-17 |
| 823462 | 61390 | 29-Apr-17 | 334 | LG 32" LED TV with Remote | 890563 | 1 | R350.00 | R350.00 | R0.00 | R105.00 | R14.70 | R230.30 | 09-Jun-17 |
| 829818 | 61304 | 29-Apr-17 | 4 | JVC 32" LCD TV | 890553 | 1 | R350.00 | R350.00 | R0.00 | R105.00 | R17.70 | R230.30 | 25-Jul-17 |
| 845126 | 61639 | 13-May-17 | 98 | AIM 17" LED Monitor | 890575 | 1 | R200.00 | R200.00 | R0.00 | R60.00 | R8.40 | R131.60 | 03-Aug-17 |
| 845126 | 61639 | 13-May-17 | 221 | LG 32" TV with remote | 890575 | 1 | R300.00 | R300.00 | R0.00 | R90.00 | R12.60 | R197.40 | 18-Aug-17 |
| 723113 | 61783 | 20-May-17 | 206 | HP Pink Mini Laptop with charger | 890595 | 1 | R225.00 | R225.00 | R0.00 | R67.50 | R9.45 | R148.05 | 05-Jun-17 |
| 840274 | 61784 | 20-May-17 | 207 | Fuji Film X100T Digital Camera | 890562 | 1 | R50.00 | R50.00 | R0.00 | R15.00 | R2.10 | R32.90 | 03-Aug-17 |
| 838913 | 62200 | 10-Jun-17 | 78 | Samsung 32" TV | 29111 | 1 | R500.00 | R500.00 | R0.00 | R150.00 | R21.00 | R329.00 | 25-Jul-17 |
| 838913 | 62474 | 24-Jun-17 | 241 | Oak double bed | 29111 | 1 | R1 900.00 | R1 900.00 | R0.00 | R570.00 | R79.80 | R1 250.20 | 25-Jul-17 |
| 838913 | 62474 | 24-Jun-17 | 243 | Defy Front Loading W/Machine | 29111 | 1 | R100.00 | R100.00 | R0.00 | R30.00 | R4.20 | R65.80 | 03-Aug-17 |
| 838913 | 62474 | 24-Jun-17 | 245 | LG Dishwasher | 29111 | 1 | R300.00 | R300.00 | R0.00 | R90.00 | R12.30 | R197.40 | 03-Aug-17 |
| 838913 | 62474 | 24-Jun-17 | 334 | Set of 4 White Leather Touch D | 29111 | 1 | R340.00 | R340.00 | R0.00 | R102.00 | R14.28 | R223.72 | 03-Aug-17 |
| 838913 | 62474 | 24-Jun-17 | 357 | Fridgemaster Fridge Freezer | 29111 | 1 | R600.00 | R600.00 | R0.00 | R180.00 | R25.20 | R394.80 | 03-Aug-17 |
| 838913 | 62474 | 24-Jun-17 | 398 | Pair Pine Bedside Pedestals | 29111 | 1 | R100.00 | R100.00 | R0.00 | R30.00 | R4.20 | R65.80 | 03-Aug-17 |
| 857055 | 62475 | 24-Jun-17 | 250 | Samsung 55" Smart LED TV | 890663 | 1 | R3 500.00 | R3 500.00 | R0.00 | R1 050.00 | R147.00 | R2 303.00 | 18-Aug-17 |
| 861416 | 62587 | 01-Jul-17 | 212.02 | LG 74cm Colour TV | 890678 | 1 | R500.00 | R500.00 | R0.00 | R150.00 | R21.00 | R329.00 | 18-Aug-17 |
| 859759 | 62692 | 08-Jul-17 | 310 | Samsung 51" 3D HD Plasma | 890671 | 1 | R300.00 | R300.00 | R0.00 | R90.00 | R12.60 | R197.40 | 18-Aug-17 |
| 860415 | 62870 | 29-Jul-17 | 151 | XBOX 360 consile with 1 x wire | 890721 | 1 | R650.00 | R650.00 | R0.00 | R195.00 | R27.30 | R427.70 | 18-Aug-17 |
| 865614 | 62910 | 29-Jul-17 | 342 | SANSUI 37" LCD TV [No Remote] | 890720 | 1 | R250.00 | R250.00 | R0.00 | R75.00 | R10.50 | R164.50 | 18-Aug-17 |
| 867889 | 62837 | 29-Jul-17 | 38 | Samsung 37" LCD TV | 890738 | 1 | R900.00 | R900.00 | R0.00 | R270.00 | R37.80 | R592.20 | 07-Sep-17 |
| 871758 | 62991 | 05-Aug-17 | 101 | Telefunken 32" TV | 890757 | 1 | R550.00 | R550.00 | R0.00 | R165.00 | R23.10 | R361.90 | 07-Sep-17 |
| 870332 | 63126 | 12-Aug-17 | 145 | Gigabyte laptop with charger | 890764 | 1 | R450.00 | R450.00 | R0.00 | R135.00 | R18.90 | R296.10 | 07-Sep-17 |
| 874914 | 63254 | 19-Aug-17 | 211 | Samsung 23" LCD Monitor | 890778 | 1 | R220.00 | R220.00 | R0.00 | R66.00 | R9.24 | R144.76 | 07-Sep-17 |
| 878708 | 63467 | 02-Sep-17 | 45.01 | Sansui 32" LED TV with Remote | 890837 | 1 | R850.00 | R850.00 | R0.00 | R255.00 | R35.70 | R559.30 | 10-Oct-17 |
| 878708 | 63467 | 02-Sep-17 | 91 | Black Desktop Computer | 890837 | 1 | R100.00 | R100.00 | R0.00 | R30.00 | R4.20 | R65.80 | 10-Oct-17 |
| 878679 | 63705 | 09-Sep-17 | 307 | SONIC 42" Plasma TV | 890867 | 1 | R50.00 | R50.00 | R0.00 | R15.00 | R2.10 | R32.90 | 10-Oct-17 |
| 886141 | 63912 | 23-Sep-17 | 161 | SAMSUNG 51" Plasma TV no remote | 890893 | 1 | R600.00 | R600.00 | R0.00 | R180.00 | R25.20 | R394.80 | 10-Oct-17 |
<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
