Excel Apply Filter on a table column Present at 4th row : Not Equal to Some Values

[KodiSrihari]

Hi Team, 

I want to apply Filter with Some Values not equal. Example "  0, 0.00, Blanks ".

The table is present at the 4th row in excel , so can't able to use Column scenario.

Any suggestions to Apply Filter, with some Values not equal.

Thanks,
Srihari

[EricNewton]

Not 100% sure what you mean by "The table is present at the 4th row in excel , so can't able to use Column scenario." so will give advice on both options.

If it is just your table starts on 4th row that should not be issue, just get the range and you can reference in the Add Criteria to Autofilter. 

Then for filtering out values you can use "<>value" so it filters out that value. If you use case is you want to filter out 0s and blanks (and your data is numbers) then the following should work.

Criteria with value of "<>0" filters out not equal to 0, Operator value of 1 is AND for Criteria2 value of "<>" filters out blanks.

If your data is not numbers and you want to filter out multiple text values like " 0" and " 0.00" I don't think the VBO offers something to do that so ideally would see about getting data formatted.

However if what you are saying is your data is a horizontal table then your best bet is to just find a way to transpose the data to get it into a standard vertical table so then you can use standard filter methods. I don't think excel VBO offers this even though you can copy and paste as transpose in excel. You could maybe load in to a collection then use collection VBO to transpose, not sure if it would work with the data.

[harish.mogulluri]

HI @KodiSrihari 

You can also copy the data from the 4th row and paste it into a new worksheet, then apply the filter.
Another approach—though I haven't tested or tried it—is to leverage Power Query and VB.NET for filtering.

[KodiSrihari]

Hi @EricNewton 

Thanks for the Detailed explanation.

Yes, i have tried the above <>0 and <>  but expected results are not coming. i can see you are passing the "Range" here as input, but i am passing "Reference " instead of "Range " Exp : Reference = K8 (Cell).

Maybe i need to change the code to provide Input as "Range " as mentioned by you. I will try.

The Sheet i which i want to apply filter on Single Column, the Value are in Number .

The Sheet is the Final BOT Output Sheet and Interconnected with Many sheets to Populate Final Values.

Thanks,

Srihari

[KodiSrihari]

Hi @harish.mogulluri 

Thanks for the approaches to try.

But as the Sheet on which i need is Interconnected to Various sheets to populate value and Formulas are present.

Copy the data from sheet to another Sheet is not possible and filter as Customer don't want to create any external sheets. Only Filter the Non Zero, Non Blank Values from One Amount Column only .

And Resulted filter data to copy & paste in to an other Standard Template.

Tried to Pass the 3 Criteria , through List as a Collection. Exact 3 Criteria Values are Filtering. But we need "No equal " the 3 Criteria Inputs.

In the 3 Field values in Collection, With Text Data type , I have given : (1)- <>         (2)- <>0   (3)- <>0.00  . But it's not working may be Through list this "<>" Not equal is not fetching.

Thanks,

Srihari

[EricNewton]

@KodiSrihari If you are able to get the data into a collection then you should be able to use the Filter Collection action from the Utility - Collection Manipulation VBO to filter out values you do not want. The query would look something like this "[YourFieldName]<>'0' AND [YourFieldName<>'' AND [YourFieldName]<>'0.0'" where each value you want to exclude be referenced with AND operator. This is assuming the collection field is text, since blanks are included.

If this doesn't help might help me to see some example screenshots to help me visualize what you are doing.

[Mohamad_747]

@KodiSrihari 

You need a CUSTOM code inside Ms excel vbo to achieve it.

It's ok for you ?

[michaeloneil]

Hi @KodiSrihari 

I think you would need to create a custom code for this as the standard filter only allows you to filter by items that exact matches. I think if you want to use Not Equal then you can only have 2 criteria in the filter using And or OR. I've added in the code for below, although this was untested, I think this should do what you need. Please note you will need to create a new action with a code stage and add in the input parameters for handle, workbookname, worksheetname, range,columnnumber,firstfilter and secondfilter. The code shown is for AND but if you need to use OR then the Operator number should be changed to 2

Dim ws as Object

ws = GetWorksheet(handle, workbookname, worksheetname)

ws.Range(range).AutoFilter(Field:=columnnumber, Criteria1:= firstfilter, Operator:=1, Criteria2:= secondfilter)

[KodiSrihari]

Hi Team,

Thanks @michaeloneil  @Mohamad_747  @EricNewton  for your Responses.

As of now for the Time Being on Business Urgency. I have used the Set Filter for the 0 and Blank Values with Filter with LIST action by providing the List of Values need to Filter.

Deleted the Filtered Value Range (Which are Not required) >  And then Removed the Filter on the Column > Copied the Required data to Destination Template > Unsaved the Source Excel File.

It worked for now. And yes, The Custom Code need to Add for this also working that.

Thanks,

Srihari