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!!
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
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
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.
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.myonlinetraininghub.com/pivot-table
Br,
Anders
@Adam Deathe, I haven't looked because Anders's answer seemed perfect.
I'll take a look for an alternative
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
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.
