Hi,
I have a spreadsheet which contains information for certain sales. Below is a basic representation of my dataset (the actual spreadsheet contains more columns of information and has new entries added daily from multiple people)
'Sales Data' worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ORDER #[/TD]
[TD]Product Code[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]102[/TD]
[TD]SKMDRT[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]103[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]104[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]105[/TD]
[TD]SKMDRT[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]106[/TD]
[TD]PDYHTW[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]107[/TD]
[TD]FTRHYGQ[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]108[/TD]
[TD]PIMHKLO[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]109[/TD]
[TD]PMYTUV[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]110[/TD]
[TD]IMRDDT[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]111[/TD]
[TD]KJULOIR[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]112[/TD]
[TD]LKJULOIR[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]113[/TD]
[TD]LKJUTYV[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
I would like to know if i could create a formula on another worksheet that will provide me with the product codes and total quantities for a specific date or date range. for example if I'm interested in the 10th October i can insert those date and it will return the following
'Data summary' worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product Code[/TD]
[TD]qty[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]SKMDRT[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
Product code SKMDRT appears once with the combined qty for the 2 sales on that date. Or if I'm interested in looking at data form the 10th - 11th it returns
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product Code[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]SKMDRT[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PDYHTW[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]FTRHYGQ[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PIMHKLO[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
I know the simplest way is to apply a filter to my dataset however i want to avoid this as the sales data has alot of information and is continuously updated, this is why i would like to create a separate sheet which can provide me with a summary for specific dates without affecting the main data. I have also tried a pivot table but that doesn't seem to work for what i am after. i have been reading up about creating an array formula with index/match but have had no luck applying to my data set.
any help would be much appreciated,
Thankyou.
I have a spreadsheet which contains information for certain sales. Below is a basic representation of my dataset (the actual spreadsheet contains more columns of information and has new entries added daily from multiple people)
'Sales Data' worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ORDER #[/TD]
[TD]Product Code[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]102[/TD]
[TD]SKMDRT[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]103[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]104[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]105[/TD]
[TD]SKMDRT[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]106[/TD]
[TD]PDYHTW[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]107[/TD]
[TD]FTRHYGQ[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]108[/TD]
[TD]PIMHKLO[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]109[/TD]
[TD]PMYTUV[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]110[/TD]
[TD]IMRDDT[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]111[/TD]
[TD]KJULOIR[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]112[/TD]
[TD]LKJULOIR[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12-Oct[/TD]
[TD]113[/TD]
[TD]LKJUTYV[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
I would like to know if i could create a formula on another worksheet that will provide me with the product codes and total quantities for a specific date or date range. for example if I'm interested in the 10th October i can insert those date and it will return the following
'Data summary' worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product Code[/TD]
[TD]qty[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]SKMDRT[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
Product code SKMDRT appears once with the combined qty for the 2 sales on that date. Or if I'm interested in looking at data form the 10th - 11th it returns
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product Code[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]SKMDRT[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]IMRDDT[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PDYHTW[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]FTRHYGQ[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PMYTUV[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]11-Oct[/TD]
[TD]PIMHKLO[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
I know the simplest way is to apply a filter to my dataset however i want to avoid this as the sales data has alot of information and is continuously updated, this is why i would like to create a separate sheet which can provide me with a summary for specific dates without affecting the main data. I have also tried a pivot table but that doesn't seem to work for what i am after. i have been reading up about creating an array formula with index/match but have had no luck applying to my data set.
any help would be much appreciated,
Thankyou.