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

Macro prob in extracting data on criteria [SOLVED]

S

SG

Member
hi Experts,


I'm in a great problem as i'm beginner in macros.I have created a template in which the data would be extracted on basis on 3 criterias.However, by using macro, i'm unable to extract it.For criterias, i have created drop-down list by using Data validation & name manager.

my Problem is that based on criteria, the selected data is not extracted.I don't know where i'm lagging behind.please help me with the solution.I have uploaded my file on below link.


http://www.2shared.com/file/Xlo4oN8Y/rough.html
 
Thanks for the reply sir bt it doesn't solve my purpose.Let me try one more time to explain.

What i want is that when i select domain, then, only name & accuracy in that domain will display on report sheet..

then ,if i select any project in that domain, then it's correponding name & accuracy will display on report sheet.

then if i select any ques in that project,then it's correponding name & accuracy will display on report sheet.

It needs 3 criteria based filters on base data which will retrieve corresponding name & accuracy

& display them on report sheet.


please sir, help me with a solution.
 
Hi ,


I have not been able to understand your requirement ; at present the criteria are used exactly the way you explain ; if any criterion is left blank , it is not used to filter.


Thus , if you specify only a domain value , the filtering is done only on the domain ; if you specify a domain value and a project value , the filtering will be done using these two criteria ; a similar thing happens if you add a third criterion.


Regarding the display of only two fields instead of all of them , I have not been able to do it ; I am not sure that it can be done , but that may be because I have Excel 2007 ; this may be possible in higher versions of Excel. Of course , I may be wrong , and it may be possible in Excel 2007.


Narayan
 
sorry for the inconvenience but i need the data as explained in above post.

If only i'm selecting domain & rest of the two criterias are blank, then no data is retrieved.It's displaying blank data.

please try this at your end & let me know if i'm doing anything wrong.
 
Hi ,


Two points :


1. I hope you are discussing with respect to the file I uploaded viz. rough.xlsm


2. I hope you are running the macro apply_filter each time you make a change to the criteria in the range E2:G2.


Can you confirm ?


Narayan
 
Yes sir,i'm using the file provided by you & running this macro each time when i change any of criteria.

I have attached the same with my issue on below link.please refer this.

http://www.2shared.com/file/KaLqmLss/rough_1_.html
 
Hi ,


The problem lies in the way you are using the criteria range ; I had intentionally changed your original criteria range , which was A4:B6 , to E1:G2 ; this is because Excel expects the criteria to be laid out horizontally , and not vertically ; all criteria on the same row will be ANDed , while multiple criteria in the same column will be ORed.


Thus , with the horizontal layout , you can have a criteria range E1:G3 , so that you can specify domain a in E2 and domain s in E3 ; the advanced filter will now show all results for domain a or domain s.


So , with this setup , you cannot enter your criteria in the range A4:B6 , and refer to them using formulae in the range E1:G2 or E1:G3. You have to manually enter them in the cells E2 , F2 , G2 ,...


Try it out like this and see.


Narayan
 
Hi Narayan,


Thanks so much for helping in my silly questions.It's working.appreciate your help.


Now my last problem is to make unique list for criterias.There are many domains & project id's which can't be entered manually so many times.I'm trying to create a unique criteria list from base data.When i'm trying to put the criterias through data validation in horizontal way in cell e1:g2,the entry of study id's are coming twice.I have used index,match & offset for creating data validation.Sheet 1 is the data for unique criteria list.Below is the link to file.

http://www.2shared.com/file/SgkdnnVs/rough_2_.html


please let me know where i'm going wrong.
 
Hi ,


Check your file here :


http://www.2shared.com/file/5gbI6-2T/rough_2.html


The formula used is from here :


http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/


Narayan
 
hey Narayan,


sorry for again disturbing bt can't do anything.


While running same macro on selecting ques no., it doesn't display required result.please look in to this.this is the same code provided by you.


please last time look in to this & try to give me a fix solution.


here is the file


http://www.2shared.com/file/2osou8re/allc.html
 
Hi ,


Please note that for the Advanced Filter to work , the column headers in the criteria range have to match the column headers of your data range !


Otherwise , when you select a value for ques , how can Excel guess that this criterion should be applied to the data in the column labelled Question ID ?


There is absolutely no problem ! Just ensure that there is an exact match between the column headers in the criteria range and those in the data range.


Narayan
 
You must log in or register to reply here.
Back
Top