I will provide you with a textual user review of an Android app, aiming to…
- User
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! Until then we're stuck with the methods below! Feel free to comment if you have another perhaps easier method.Also note -- in this setup I've named the first three columnsItemColAreaColandPriceCol. I've named the green Area lookup cellMyArea. 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 likeDataTable[Item]instead ofA2:A23.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 or here -- 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 inRow(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)= 2Row(A3)= 3 etc. No matter where in your spreadsheet you put this formula it will always have thatRow(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))) ")}# NMATCHNMATCH is a user-defined function written by /u/Excelevator it can be found here as well as its companion NMATCHIFS 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 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"")) ")# 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=82261dd6e3afcafbfdf8653a451a49a0874e5e03Hopefully 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: - Assistant
Irrelevant: The review provided is a detailed explanation and guide on creating dynamic filtered lists in Excel. It does not mention bugs, errors, or requests related to any specific features of an Android app. Instead, it offers a comprehensive tutorial on a method within Excel, which does not align with the classification criteria of bug reports or feature-related feedback for an Android app.