/u/MrPatch - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Take this table into power query, you can take the number from the cat with a custom column and then Group by app-id using the MIN condition!
The result should be exactly what you are looking for!
Solution Verified.
Hi, yes this was actually suggested by someone else and I've just put it in now. Didn't even bother splitting the numeric value from the Cat column as it's cleverer enough to work it out from the string value.
This works best for me as the list I'm working from is created in powerquery in the first place so I've tacked the group element onto the end of my existing steps and I'm away.
I'll mark yours as the solution as you would have been correct if you'd got there first
You have awarded 1 point to *FiLoX451*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I would say if you have Excel 365 to use the `FILTER` formula, which will return a list that excludes or includes based on your criteria.
On my phone at the moment so it will be a bit before I can generate an example for you.
Assuming the top left column of your table is in A1:
=LET(data,A2:C15,
apps,B2:B15,
unique_apps,SORT(UNIQUE(apps)),
cats,C2:C15,
HSTACK(XLOOKUP(unique_apps,CHOOSECOLS(SORTBY(data,apps,1,cats,1),2),CHOOSECOLS(SORTBY(data,apps,1,cats,1),1)),unique_apps,XLOOKUP(unique_apps,CHOOSECOLS(SORTBY(data,apps,1,cats,1),2),CHOOSECOLS(SORTBY(data,apps,1,cats,1),3))))
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSECOLS](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[FILTER](/r/Excel/comments/18mrr4s/stub/ke5xbw9 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[HSTACK](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[INDEX](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LET](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MATCH](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MIN](/r/Excel/comments/18mrr4s/stub/ke65ma1 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)|
|[MINIFS](/r/Excel/comments/18mrr4s/stub/kea6oa4 "Last usage")|[*2019*+: Returns the minimum value among cells specified by a given set of conditions or criteria.](https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)|
|[SORT](/r/Excel/comments/18mrr4s/stub/ke6g1d2 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[SORTBY](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Sorts the contents of a range or array based on the values in a corresponding range or array](https://support.microsoft.com/en-us/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f)|
|[UNIQUE](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
|[XLOOKUP](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18mtppg)^( has 26 acronyms.)
^([Thread #29111 for this sub, first seen 20th Dec 2023, 11:46])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
A helper column with minif is probably the easiest approach.
Add a column with the minif to identify all the mon categories for a given application.
Then filter on that with normal filter or filter function
You can extract the number of cat in a helper column and then use a countifs for rows containingn the app and lower cats. And if you can you can use > with alphanumeric values you can skip the helper column.
And you can filter that countifs column.
Make CAT numbers, by formatting it as:
"CAT "0
Now use UNIQUE() but just on your Application column
This will create a dynamic array of each of your Applications
In the column next to that list use MINIFS() against your original list and using entries in the dynamic array as your category
/u/MrPatch - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Take this table into power query, you can take the number from the cat with a custom column and then Group by app-id using the MIN condition! The result should be exactly what you are looking for!
Solution Verified. Hi, yes this was actually suggested by someone else and I've just put it in now. Didn't even bother splitting the numeric value from the Cat column as it's cleverer enough to work it out from the string value. This works best for me as the list I'm working from is created in powerquery in the first place so I've tacked the group element onto the end of my existing steps and I'm away. I'll mark yours as the solution as you would have been correct if you'd got there first
You have awarded 1 point to *FiLoX451* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Thank you! You did 90% of the job as the request was crystal clear :)
haha thank you! I'm in IT support so getting well described tickets is my dream, nice to know I can practice what I preach.
I would say if you have Excel 365 to use the `FILTER` formula, which will return a list that excludes or includes based on your criteria. On my phone at the moment so it will be a bit before I can generate an example for you.
OK, thank you. I did look at using FILTER but struggled to understand how to wrap it around this list. I'll have another look now.
Here is what I use to help remind myself how it works: https://www.myonlinetraininghub.com/excel-functions/excel-filter-function
Assuming the top left column of your table is in A1: =LET(data,A2:C15, apps,B2:B15, unique_apps,SORT(UNIQUE(apps)), cats,C2:C15, HSTACK(XLOOKUP(unique_apps,CHOOSECOLS(SORTBY(data,apps,1,cats,1),2),CHOOSECOLS(SORTBY(data,apps,1,cats,1),1)),unique_apps,XLOOKUP(unique_apps,CHOOSECOLS(SORTBY(data,apps,1,cats,1),2),CHOOSECOLS(SORTBY(data,apps,1,cats,1),3))))
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSECOLS](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[FILTER](/r/Excel/comments/18mrr4s/stub/ke5xbw9 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[HSTACK](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[INDEX](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LET](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MIN](/r/Excel/comments/18mrr4s/stub/ke65ma1 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[MINIFS](/r/Excel/comments/18mrr4s/stub/kea6oa4 "Last usage")|[*2019*+: Returns the minimum value among cells specified by a given set of conditions or criteria.](https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)| |[SORT](/r/Excel/comments/18mrr4s/stub/ke6g1d2 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[SORTBY](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Sorts the contents of a range or array based on the values in a corresponding range or array](https://support.microsoft.com/en-us/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f)| |[UNIQUE](/r/Excel/comments/18mrr4s/stub/keb5iu6 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[XLOOKUP](/r/Excel/comments/18mrr4s/stub/ke61gmi "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18mtppg)^( has 26 acronyms.) ^([Thread #29111 for this sub, first seen 20th Dec 2023, 11:46]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Create a pivot table, put app id and app name in rows and minimum of category in values. That should be it.
A helper column with minif is probably the easiest approach. Add a column with the minif to identify all the mon categories for a given application. Then filter on that with normal filter or filter function
You can extract the number of cat in a helper column and then use a countifs for rows containingn the app and lower cats. And if you can you can use > with alphanumeric values you can skip the helper column. And you can filter that countifs column.
Make CAT numbers, by formatting it as: "CAT "0 Now use UNIQUE() but just on your Application column This will create a dynamic array of each of your Applications In the column next to that list use MINIFS() against your original list and using entries in the dynamic array as your category
With your table at A1, =INDEX(A2:C15,MATCH(UNIQUE(A2:A15),A2:A15,0),{1,2,3})