1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data collection from database based on multiple criteria

Discussion in 'Ask an Excel Question' started by samto22, Dec 9, 2016.

  1. samto22

    samto22 New Member

    Messages:
    7
    Hello!

    I've a problem and I'm stucked in finding of it's solution. Please refer to attached file. There's a sheet "Data" where data are inputted and a sheet "CollectedData" in which I need to generate report from "Data" sheet by month criteria. I've entered manually how report should look. Kinldy please help.

    Thanks in advance, Tomas

    Attached Files:

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi @samto22

    A pivot table perhaps
    Please refer to attached

    Hope it helps

    Attached Files:

    Thomas Kuriakose likes this.
  3. samto22

    samto22 New Member

    Messages:
    7
    Hi PCosta,

    Thanks for quick reply.

    I added more drivers but pivot table did not worked in order.

    for example Driver 2: i need to show:
    Driver 2 2016.11.02-2016.11.03 Poland
    but report show two rows:

    Driver 2 2016.11.02-2016.11.02 Poland
    Driver 2 2016.11.03-2016.11.03 Poland

    I've attached file.

    Attached Files:

  4. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    That is due to the fact that the formula detects the start date and end date by looking at two consecutive lines and comparing both driver and country... if consecutive lines have same driver and country, the formula will keep that initial date until that criteria is no longer met which would happen when you have another driver or country.
    In this new scenario, the formula will not work because these two lines are separated... thus the formula understands it as being two separate events.

    If I can figure out another way to do it I will let you know, but for now the only solution would be to place the line with Driver2-Poland-03-11-2016 right after the Driver2-Poland-02-11-2016... This way the formula will recognize it as being a single event.
  5. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    331
    Hi to all!

    Please check the file. Blessings!

    Attached Files:

  6. samto22

    samto22 New Member

    Messages:
    7
    Hello John,

    Thank you! It's works perfect.
  7. samto22

    samto22 New Member

    Messages:
    7
    Hello John,

    Could you please help me to find solution to my problem. The problem is that additional condition should be added - Max date cannot be greater than last month day.

    For example if Driver4 was in the same country some days in a row but in different months, report should generate periods for the same month.

    List of dates, driver and country:

    2016.11.29 Driver 4 Germany
    2016.11.30 Driver 4 Germany
    2016.12.01 Driver 4 Germany
    2016.12.02 Driver 4 Germany

    Report should show:

    Driver 4 2016.11.29 2016.11.30 Germany
    Driver 4 2016.12.01 2016.12.02 Germany




    Please refer to attached file.
    Thanks in advance!

    Attached Files:

  8. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    331
    Hi again samto22!

    Check file with the change. Blessings!

    Attached Files:

    samto22 and Thomas Kuriakose like this.
  9. samto22

    samto22 New Member

    Messages:
    7

Share This Page