Advanced filter and basic user ....

[AntoPath]

Hi !
I have to filter a table made of 12 columns and a variable number of rows, usually 3000. These rows are extracted from a hospital LIS and correspond to diagnoses made to single patients on which we have to perform monthly a review for Quality Control.
I need to extract only those patients whose diagnoses contain one or more word in the column "Diagnosi", but the problem is that even if I select the data, criteria range and the destination cell (in the same sheet), I cannot extract any record but the name of the columns.
I'm sure, given my deep incompetence, the problem is trivial and the solution is under my eyes, but .... believe me I cannot find a solution and my only chance is to ask you, my heroes, a big help.
Thanks a lot !!
Antonio

 

[NARAYANK991]

Hi ,

I am not able to resolve your problem , but I would like to put down my observations.

1. The criteria , as specified , will never work.

The text in the column labelled Diagnosi is long text where the words you have specified in the Criteria range are embedded.

Thus to correctly specify the criteria , you will have to use formulas as in :

="*" & "dermatite" & "*"

Similar formulas will replace all the other words.

2. I tried by using this formula for text in the column labelled ID Ente , where I used the criterion :

="*" & "99 Referto" & "*"

This works.

Strangely enough data from all the columns , with the exception of the column labelled Diagnosi is extracted. Can you go through the data in the column to see what the length of the longest entry is ? I checked it and found it to be 7962 characters. Probably this is causing the problem.

See the attached file.

Narayan

 

[Chihiro]

Diagnosi column header has one major issue.

It has some 8000 or so single space after Diagnosi. This is causing issue with recognizing criteria range as well as copy to range.

Delete column header for "Diagnosi" and type it back in. Then copy it to Criteria and CopyTo range header as well.

This will fix your issue (along with using Narayank's solution of adding * in front and end of the search string.

 

[AntoPath]

Great !
The problem was that the xls file downloaded from the LIS for a given interval (in this case for the month of October: 10/1/2019:10/31/2019) is full of HTML hidden tags (<p>, </p>, <&nbsp;>). Clearing the file from this garbage and putting the wild card "*" before and after the words to be found, is the solution.
Thanks a lot Narayan, thanks a lot Chihiro, you are simply the best !.