0

I have the below data set in excel which I am trying to filter out based on the value in next column.

What I am trying to do here is if there is any 0 values present in SH-001, the whole record set will filter out from the list.

Data

Stye Inventory
SH-001 1
SH-001 1
SH-001 0
SH-002 2
SH-002 3
SH-002 4

Desired out put

Style Inventory
SH-002 2
SH-002 3
SH-002 4

Any suggestions will be more than welcome.

2

Next solution - using FILTER-function (Office 365) and defined table (insert table > named tblData).

=FILTER(tblData;MINIFS(tblData[Inventory];tblData[Stye];tblData[Stye])<>0)

enter image description here

MINIFS(tblData[Inventory];tblData[Stye];tblData[Stye]) acts like a helper column to tblData that returns per each row the min-value of the according Stye - which then can be used for the main filter

1

You can do this using Conditional Formatting in Excel. Assuming that the columns "Style" & "Inventory" are in column A & B respectively in your excel, you need to apply the following conditional formatting on entire column A

=COUNTIFS($A:$A,$A1,$B:$B,0)>0

This will highlight all the cells that match the above condition. Then you can use "Filter by color" feature in excel.

Sample Screenshot showing conditional formatting

2
  • Thanks, can we also add one more criteria to it 5 hours ago
  • You can add as many criterias as you'd like to. You should be able to find some resources online
    – Gangula
    1 hour ago
0

Maybe this helps you; it's for Googles spreadsheets, but the way to go is probably similar: OTOMOTIF/a/25903892/3249027 The linked example is about conditional formatting and not filtering per se, but you could "format" the data of the undesired set by multiplying it with zero *0 and then simply filter all 0s in a next step.

Not the answer you're looking for? Browse other questions tagged or ask your own question.