• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Automatically sorting data from a table into groups and to do so in alphabetical order.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Automatically sorting data from a table into groups and to do so in alphabetical order.|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Automatically sorting data from a t…
sp_PrintTopic sp_TopicIcon
Automatically sorting data from a table into groups and to do so in alphabetical order.
Avatar
Adam Deathe

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 31, 2023
sp_UserOfflineSmall Offline
1
January 31, 2023 - 5:50 am
sp_Permalink sp_Print

I am trying to sort individuals into groups. Their membership is ranked in importance from 1 (most important) to 3 (unnecessary). Is there a formula that would return the member to a group based on the numeric value of the corresponding cell?

The chart is interactive so that I can change the member's ranking in a group as needs be. This change in ranking should alter their position in the group level - 1 (essential) through to 3 (unnecessary). Ideally, I would like to be able to have the data returned so that returns for 1 and 2 are combined with 3 separated. I imagine that this would be 'simply' inserting an or statement between the two formula that would sort the date into Level 1 and Level 2.

Could the data be listed in alphabetical order when it is sorted into the groups and update itself if a ranking (1-3) is changed?

Thank you in advance!!

Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 70
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
2
January 31, 2023 - 5:36 pm
sp_Permalink sp_Print

Hi adam, just a tip, you've added your explanation to the file entering text in different cells.
To make it easier for yourself (and others) I suggest you insert a textbox and type the text there.
About your question; I've downloaded the file and will see if I can help you

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
February 1, 2023 - 6:12 am
sp_Permalink sp_Print

Hello,

If you want you can use pivot tables instead, for that you then need to rearrange the data to a tabular format, see attached file for an example.
This is a simpler approach wich gives you flexibility to rearrange how the data is presented without to much work.

Br,
Anders

Avatar
Adam Deathe

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 31, 2023
sp_UserOfflineSmall Offline
4
February 2, 2023 - 12:30 am
sp_Permalink sp_Print

Thank you Hans and Anders. I look forward to hearing form you Hans. Anders, I appreciate the pivot table but can it update the group filing when I change the importance in the pivot table? I have downloaded the file and it doesn't do anything to the tables above when I change any of the importance rankings.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
February 2, 2023 - 3:51 pm
sp_Permalink sp_Print

Hello,

You need to update the pivot table after any changes made in the source table, just right click in the pivot and choose update. You can read more about pivot tables in this blog article, link below.

https://www.myonlinetraininghu.....ivot-table

Br,
Anders

Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 70
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
6
February 3, 2023 - 5:02 pm
sp_Permalink sp_Print

@Adam Deathe, I haven't looked because Anders's answer seemed perfect.
I'll take a look for an alternative

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
February 4, 2023 - 8:05 am
sp_Permalink sp_Print

Hello,

I took this as an oppurtunity to test myself if I remembered how to twist the functions like in the old days. I would choose the pivot table option, as it just takes a few minutes to get, now I sat almost 40 minutes for this example.

The formula example in attached file seems to work, but need adjustment if you ever decide to expand the data range and options. With a pivot table you just update and you get the new values direct.

This excercise was good in the way it reminded me how I used to work in Excel and that I am glad I took some courses here at MOTH and leveled up my skills.

I hope this helps you with your task.

Br,
Anders

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
February 4, 2023 - 4:51 pm
sp_Permalink sp_Print

Couldn't resist to add another solution that doesn't require complicated formulas or rearranging of the data in an Excel sheet.

I used Power Query that takes the data as is, rearranges/unpivots it and then uses filtering and grouping to create the tables for each level.

So, I went all the way with PQ, but you could also just unpivot the data in PQ, load the flattened data table back to Excel and then continue the way Anders recommended in post # 3. I.e. using a simple pivot table.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Michelle Neven
Andrew Kuhn
Angela Paul
Forum Stats:
Groups: 3
Forums: 24
Topics: 6206
Posts: 27202

 

Member Stats:
Guest Posters: 49
Members: 31875
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.