• 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...

  • 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

samto22

New Member
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
 

Attachments

  • Drivers.xlsx
    15.3 KB · Views: 9
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
Hi @samto22

A pivot table perhaps
Please refer to attached

Hope it helps
 

Attachments

  • Drivers.xlsx
    21.8 KB · Views: 6
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.
 

Attachments

  • Drivers.xlsx
    22.4 KB · Views: 5
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.
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.
 
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!
 

Attachments

  • Drivers.xlsx
    23.6 KB · Views: 2
Back
Top