How to use FILTER to return rows if its value is different than the above row in Google Sheets?-CodePudding

I'm trying to make a "friendly" view of a more complicated sheet by filtering out unnecessary data. I want one of the filter conditions to be to return a value only if that value is different than the previous row's value.

I've tried doing a filter, such as =filter(B:B, B2 <> B1) which obviously doesn't resolve. How do I do an offset reference so that the results are similar to as follows:

Sample Data

| Row | Category | Subcategory |
| --- | -------- | ----------- |
| 1   | Accounts | Ac1         |
| 2   | Accounts | Ac2         |
| 3   | Accounts | Ac3         |
| 4   | Accounts | Ac4         |
| 5   | Feedback | FbA         |
| 6   | Feedback | FbB         |
| 7   | Feedback | FbC         |
| 8   | Feedback | FbD         |
| 9   | Profile  | PfOne       |
| 10  | Profile  | PfTwo       |
| 11  | Profile  | PfThree     |

Desired Result

| Row | Category | Subcategory |
| --- | -------- | ----------- |
| 1   | Accounts | Ac1         |
| 5   | Feedback | FbA         |
| 9   | Profile  | PfOne       |

CodePudding user response：

Try:

={A1:C1;FILTER(A2:C,B1:INDEX(B:B,COUNTA(B:B),1)<>B2:B)}

CodePudding user response：

Here is another approach (assuming that your posted "Row" column is not actually part of your data, but that your "Category" header is, in fact, in A1):

=ArrayFormula({A1:B1;FILTER(A2:B,A2:A<>INDIRECT("A1:A"&ROWS(A:A)-1))})

CodePudding user response：

go for:

=SORTN(A1:C, 9^9, 2, 2, 1)