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

Advanced filter doesn't retrieve accurate data

Asfandyar Ali

Asfandyar Ali

Member
Dear Ninjas,
I want to filter worksheet 1 "Column A" data in worksheet 2. I tried with advanced filter option but it doesn't filter correctly as can be seen in the attached file.

Can somebody please help with the right formula or Macro to resolve the issue.

Many thanks,

Ali
 

Attachments

  • advanced filter case.xlsx
    498.2 KB · Views: 1
Hi,
Sorry if I am bothering.
If you see the workbook. After applying advanced filter, it filters extra data which is not listed in Worksheet 1, but it fetches from nowhere?
10210039168-20
10210039168-25
10210039168-26
10210039168-27

Could you please help
 
You have hidden a huge number of rows. A2:A1428.But have included them in the filter.
 
Hi Bob.
Thank you for your reply. I applied advanced filter on worksheet 2, it selected all data range and than on criteria I selected column A in worksheet 1 and it displayed the result which is shown in the attachment.

I did not hide the rows, applying advanced filter did. How can I correctly apply advanced filter to get my result or is there any other way.

Many thanks
 
Hi Narayan,
Thank you for your quick response.

It still extracts unwanted data which is not mentioned in worksheet 1 column A as listed below

10210038031-20
10210038031-21
10210038031-22
10210038031-26
10210038031-27
10210038031-40
10210038031-41
10210038031-43
10210038031-44
10210039168-20
10210039168-21
10210039168-23
10210039168-24
10210039168-25
10210039168-26
10210039168-27
10210039168-28
10210039168-29
 
Hi Narayan,
Means 10210038031-2 and 10210038031-2x (last digits after 2) is creating the problem? Is there any way to resolve this?
 
Hi Ali,

If you apply auto filter and type in the box 10210038031-2, it will filter all rows in which PO numbers starts with this so 21,22,23 .... all will come. I think advance filter also does the same thing and that's why you are getting all these unwanted result.

Regards,
 
Try putting the criteria as:

'=10210038031-2

for all the criteria value.

EDIT: See the file.

Regards,
 

Attachments

  • advanced filter case.xlsx
    545.1 KB · Views: 0
Last edited:
The Criteria is on SHeet 1 Column A.
I had put ="="&A2 in C2 and copy down. Now copy column C and paste values only in cell A2, now applied advance filter on the data using column A as criteria range.

This way advance filter is only filtering 10210038031-2 (e.g.) values only and not other values.

Regards,
 
Thanks for the help Somendra and Narayan. I will see if it goes ok with my data. I will contact in case i need further help.
Much appreciated your valuable help.
 
You must log in or register to reply here.
Back
Top