• 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 Extraction excel challenge

Jack sparrow

New Member
Create a formula for data extraction with multiple conditions and various combination of multiple conditions such that where all the cells determining condition is left blank then that means no such condition exist. not necessary for that formula to provide solution if those conditions create a cascading effect

here I am uploading excel file with a solution but formulas have been deleted

as a matter of fact it took me 3 days to create such a formula
 

Attachments

  • Book1.xlsx
    29.2 KB · Views: 75
Jack

Firstly, Welcome to the Chandoo.org Forums

Do you realise that you can use an Advanced Filter on that directly as it is?

Goto the Data, Filter, Advanced menu

Fill out the Advanced Filter dialog as below

upload_2016-6-8_22-13-3.png
Ok

Enjoy
 
Here i am uploading my file with formulas
Seriously if anyone knows formula shorter than this then please help me
 

Attachments

  • BookXYZ.xlsx
    297.1 KB · Views: 65
sorry bro your excel file doesn't get the task fulfilled, but thank you for your time
anyway if you want here i had done it and it works exactly the way those advance filters work, the reason i had not used advance filters was because i needed dynamism
 

Attachments

  • Bookbook.xlsx
    71.5 KB · Views: 25
Hi All....
I think the easiest way to do the same by converting the range into table and insert slicer, then click on slicer based on requirement. If we are using office 2013 or higher.
 
sorry bro your excel file doesn't get the task fulfilled, but thank you for your time
anyway if you want here i had done it and it works exactly the way those advance filters work, the reason i had not used advance filters was because i needed dynamism

Is it possible to do this with a date range. I have a similar excel file and I want to extract data from a table within a date range and another criteria

Thanks
 
sorry bro your excel file doesn't get the task fulfilled, but thank you for your time
anyway if you want here i had done it and it works exactly the way those advance filters work, the reason i had not used advance filters was because i needed dynamism

Mr. Sparrow.
I have a similar excel file but less complicated. I tried using some of your formulas but failed. Can you help with my issue.

I have a table in enter_data sheet tab and want to display this data to invoice tab sheet within a date range and the Hauler.

Please help
 

Attachments

  • TEST_UPDATED_HAULERS.xlsx
    38.5 KB · Views: 6
Hi, I do not pretend that the attached file is an answer to your problem.
It deals only with 4 fully-completed conditions. The point of interest might be that the entire result is produced using a single array formula. Note: there is nested within it a further named formula; see Name Manager.

It is shorter though!
 

Attachments

  • BookXYZ (array).xlsx
    366.3 KB · Views: 4
This might be closer. I came to the conclusion that a blank criterion field matches anything but if the entire condition is blank then it should be ignored.
 

Attachments

  • BookXYZ (array).xlsx
    363.9 KB · Views: 21
Back
Top