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.
CodePudding user response：
Next solution - using FILTER-function (Office 365) and defined table (insert table > named tblData).
=FILTER(tblData;MINIFS(tblData[Inventory];tblData[Stye];tblData[Stye])<>0)
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
CodePudding user response：
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.
CodePudding user response：
Maybe this helps you; it's for Googles spreadsheets, but the way to go is probably similar: https://stackoverflow.com/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.