System: I will provide you with a textual user review of an Android app, aiming to categorize the reviews into specific intention classes:
Bug report: All posts that report on bugs and errors of a software application.
Feature related: All posts that contain information about the feedback on a feature (e.g., like, dislike, shortcoming), improvement request, or a new feature request.
Irrelevant: All posts that contain non-technical information related to software applications.
User: 'prompt:I will provide you with a textual user review of an Android app, aiming to categorize the reviews into specific intention classes:
Bug report: All posts that report on bugs and errors of a software application.
Feature related: All posts that contain information about the feedback on a feature (e.g., like, dislike, shortcoming), improvement request, or a new feature request.
Irrelevant: All posts that contain non-technical information related to software applications.
Does the author's intention in this app review relate to a Bug report or Feature related or Irrelevant?
review: How to create a dynamic filtered list. # How to create a dynamic filtered list -- three methodshttps://preview.redd.it/au8i3a0iq0w21.png?width=513&format=png&auto=webp&s=929376cd603a724da5b7e9250cc8c31a3a9a887eSo you have a table of data I want to point out that Excel 365 will at some point be introducing the `FILTER()` function which will make this extremely easy -- you can make a dynamic filtered list with a single short formula in one cell like [this](https://i.imgur.com/Xva1lp4.gifv)! Until then	we're stuck with the methods below! Feel free to comment if you have another	perhaps easier method.&#x200B;Also note -- in this setup I've named the first three columns `ItemCol`	`AreaCol`	and `PriceCol`. I've named the green Area lookup cell `MyArea`. Naming your ranges can make these formulas much easier to write. Better yet	you should format your data as a Table (Ctrl+T) and then you can reference columns by their name automatically	like `DataTable[Item]` instead of `A2:A23`.&#x200B;&#x200B;https://preview.redd.it/2gxvylujle231.png?width=557&format=png&auto=webp&s=de82dac1d6cadb2713ba3d54ccea28d52cb20cbb# Nth Match Array FormulaI'm going to assume here know how VLOOKUP and INDEX/MATCH work -- so you know you could use INDEX/MATCH in this scenario to find the first occurrence of North	and pull back the data:    =INDEX(ItemCol	Match(Area	AreaCol	0)    =INDEX(AreaCol	Match(Area	AreaCol	0)    =INDEX(PriceCol	Match(Area	AreaCol	0)We need a trick to get not just the first match	but *all* the matches -- so we use a hacky technique called an Nth-Matching.    {=IFERROR(INDEX(ItemCol	SMALL(IF(AreaCol=MyArea	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1)	ROW(A1)))	")}    {=IFERROR(INDEX(AreaCol	SMALL(IF(AreaCol=MyArea	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1)	ROW(A1)))	")}    {=IFERROR(INDEX(PriceCol	SMALL(IF(AreaCol=MyArea	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1)	ROW(A1)))	")}These are array formulas	so they must be entered with **CTRL+SHIFT+ENTER**.You can read about the details of this function [here](https://www.spreadsheetweb.com/excel-formula-for-filtering-data/) or [here](https://exceljet.net/formula/get-nth-match-with-index-match) \-- basically this array formula is able to compile a list of all the row numbers that match your criteria	then it returns just the Nth smallest value from that list of row numbers. I like to plug in `Row(A1)` for the ""N"" term; this way	I get the 1st match the first time I use the formula	and then the next match each time I drag the formula down -- `Row(A2)` = 2	`Row(A3)` = 3	etc. No matter where in your spreadsheet you put this formula	it will always have that `Row(A1)` reference in the first cell before you start dragging it down.Because we don't necessarily know how many matches there may be for our search term	we use IFERROR around all the functions to just return a blank if the function errors out.Remember this is an array formula so it must be entered with CTRL+SHIFT+ENTERYou can nest more IF statements to meet multiple conditions -- for example	lets say I also only wanted prices above 50:    {=IFERROR(INDEX(ItemCol	SMALL(IF(AreaCol=MyArea	IF(PriceCol>50	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1))	ROW(A1)))	")}    {=IFERROR(INDEX(AreaCol	SMALL(IF(AreaCol=MyArea	IF(PriceCol>50	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1))	ROW(A1)))	")}    {=IFERROR(INDEX(PriceCol	SMALL(IF(AreaCol=MyArea	IF(PriceCol>50	ROW(AreaCol)-ROW(INDEX(AreaCol	1))+1))	ROW(A1)))	")}&#x200B;# NMATCHNMATCH is a user-defined function written by /u/Excelevator	it can be found [here](https://www.reddit.com/r/excelevator/comments/arxwh4/udf_nmatch_value_range_instance_optional/) as well as its companion [NMATCHIFS](https://www.reddit.com/r/excelevator/comments/as5ws8/udf_nmatchifs_range_instance_closest_match/) to add more criteria.This function works very similar to regular MATCH	except with and Nth term. It will accomplish exactly what we did above	but with a lot less complication. You need to [add the code to a module in the Visual Basic editor](https://www.contextures.com/xlvba01.html)	which also requires you to save the file as a .XLSM -- a small price to pay for a lot more convenience. As with the last example	we wrap this function in IFERROR to return a blank when we run out of matches.    =IFERROR(INDEX(ItemCol	NMATCH(MyArea	AreaCol	ROW(A1)	0))	")    =IFERROR(INDEX(AreaCol	NMATCH(MyArea	AreaCol	ROW(A1)	0))	")    =IFERROR(INDEX(PriceCol	NMATCH(MyArea	AreaCol	ROW(A1)	0))	")Much cleaner than the Nth match array formula!And for that extra criteria of Prices above 50 -- you could add as many criteria as you want here:    =IFERROR(INDEX(ItemCol	NMATCHIFS(MyArea	AreaCol	ROW(A1)	0	PriceCol	>50""))	")    =IFERROR(INDEX(AreaCol	NMATCHIFS(MyArea	AreaCol	ROW(A1)	0	PriceCol	>50""))	")    =IFERROR(INDEX(PriceCol	NMATCHIFS(MyArea	AreaCol	ROW(A1)	0	PriceCol	>50""))	")&#x200B;# PowerQueryThis method relies on some dynamic PowerQuery code rather than using formulas -- so you'll need Excel 2016+. Make sure you've given that criteria cell (i.e. North in this example) a name. It'll also require a short macro if you want it to refresh automatically.Step 1 is to select your table of data then go to the Data tab	Get Data from Table/Range.Your data table will open in PowerQuery. Next just filter the desired column (or columns) based on any criteria you want	doesn't matter at this stage:https://preview.redd.it/re01oetkx0w21.png?width=1053&format=png&auto=webp&s=43b3b3452f4ab646c130724e54dc2697eb7663adNow head into the ""Advanced Editor"" next to Refresh Preview	here's where we're going to make this dynamic. You're going to see code that looks something like this:    let        Source = Excel.CurrentWorkbook(){[Name=""DataTable""]}[Content]	#""Changed Type"" = Table.TransformColumnTypes(Source	{{""Item""	type text}	{""Area""	type text}	{""Price""	Int64.Type}})	#""Filtered Rows"" = Table.SelectRows(#""Changed Type""	each ([Area] = ""North""))    in        #""Filtered Rows""On that third row	you can see it's ""hard coded"" to filter the \[Area\] column to ""North"". We want to replace North with a variable that it pulls from the named range MyArea. To do this	we add a line to declare a variable called AreaCriteria	then change ""North"" in that code to AreaCriteria:    let        AreaCriteria = Excel.CurrentWorkbook(){[Name=""MyArea""]}[Content][Column1]{0}	Source = Excel.CurrentWorkbook(){[Name=""DataTable""]}[Content]	#""Changed Type"" = Table.TransformColumnTypes(Source	{{""Item""	type text}	{""Area""	type text}	{""Price""	Int64.Type}})	#""Filtered Rows"" = Table.SelectRows(#""Changed Type""	each ([Area] = AreaCriteria))    in        #""Filtered Rows""That's it! Just like the other examples	you could filter other columns for other criteria	then just add more variables/named ranges to this code. Load this table to your workbook and cut/paste to move it to the right spot.You will have to remember to click ""refresh"" when the criteria is changed to tell PowerQuery to look the data up again. I get my query to refresh automatically when the criteria is changed with this code	added to the worksheets code (Right click the worksheet name and ""view code""):    Private Sub Worksheet_Change(ByVal Target As Range)        If Not Intersect(Target	Range(""MyArea"")) Is Nothing Then ThisWorkbook.RefreshAll    End SubOr if you have multiple queries	just refer to yours by name:    Private Sub Worksheet_Change(ByVal Target As Range)        If Not Intersect(Target	Range(""MyArea"")) Is Nothing Then Thisworkbook.Connections(""Query - DataTable"").Refresh    End Subhttps://preview.redd.it/moeo5dyez0w21.png?width=873&format=png&auto=webp&s=82261dd6e3afcafbfdf8653a451a49a0874e5e03&#x200B;Hopefully someone finds this useful -- have found myself answering this question many times on r/Excel and thought it would be nice to compile the answer in one place.Happy Excelling!"																											'
intention: '
Assistant: