Results 1 to 2 of 2

Thread: Obtaining sets of data that fall on a specific date or withing a specific date range.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Obtaining sets of data that fall on a specific date or withing a specific date range.

    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
    Date ORDER # Product Code Qty
    10-Oct 102 SKMDRT 5
    10-Oct 103 IMRDDT 10
    10-Oct 104 PMYTUV 7
    10-Oct 105 SKMDRT 3
    11-Oct 106 PDYHTW 4
    11-Oct 107 FTRHYGQ 15
    11-Oct 108 PIMHKLO 5
    11-Oct 109 PMYTUV 12
    12-Oct 110 IMRDDT 3
    12-Oct 111 KJULOIR 2
    12-Oct 112 LKJULOIR 2
    12-Oct 113 LKJUTYV 9

    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
    Date Product Code qty
    10-Oct SKMDRT 8
    10-Oct IMRDDT 10
    10-Oct PMYTUV 7

    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
    Date Product Code QTY
    10-Oct SKMDRT 8
    10-Oct IMRDDT 10
    10-Oct PMYTUV 7
    11-Oct PDYHTW 4
    11-Oct FTRHYGQ 15
    11-Oct PMYTUV 12
    11-Oct PIMHKLO 5

    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.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    5,008
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Obtaining sets of data that fall on a specific date or withing a specific date range.

    A Pivot Table will do exactly what you want.

    v A B C D E F G H
    1 Date ORDER # Product Code Qty
    2 43748 102 SKMDRT 5 Date Product Code Sum of Qty
    3 43748 103 IMRDDT 10 43748
    4 43748 104 PMYTUV 7 IMRDDT 10
    5 43748 105 SKMDRT 3 PMYTUV 7
    6 43749 106 PDYHTW 4 SKMDRT 8
    7 43749 107 FTRHYGQ 15 43749
    8 43749 108 PIMHKLO 5 FTRHYGQ 15
    9 43749 109 PMYTUV 12 PDYHTW 4
    10 43750 110 IMRDDT 3 PIMHKLO 5
    11 43750 111 KJULOIR 2 PMYTUV 12
    12 43750 112 LKJULOIR 2 43750
    13 43750 113 LKJUTYV 9 IMRDDT 3
    14 KJULOIR 2
    15 LKJULOIR 2
    16 LKJUTYV 9
    17
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •