0
To illustrate, let's say we have a range of 'sheet1'!$B$2:$F$8 that looks like:
Dennis Charlie Frank Dennis Aaron
Charlie Frank Erik Aaron Charlie
Aaron Dennis Charlie Charlie Dennis
Bill Aaron Hank
Hank
Dennis
Bill
...wherein each column is a separate person's ranking of preference among various possible choices.
The goal here is to create a list of all unique ranked choices and put them into single column on a different sheet, ideally in alphabetical order (so A1 on sheet2 would be "Aaron", 'sheet2'!A2 would be "Bill", &c).
The pool of all available choices changes regularly and includes dozens of possible choices that ultimately won't be ranked, and the rankings themselves change often as well (including who is/isn't ranked), so this list needs to be generated using (and automatically update from) the above range to avoid wasting time manually updating it and wasting space on unranked choices.
Any idea how to go about doing this?
Edit: Partial solution found:
After a lot of searching around I found this Google Docs Help Forum post and was able to adapt it to do what I want it to in Google Sheets by putting the following formula into A1 on sheet2:
=sort(unique(transpose(split(ArrayFormula(concatenate(Sheet1!B2:F&"|")),"|"))))
Unfortunately this doesn't work in my preferred software (LibreOffice Calc) and I have no idea how to use macros to make it work, but if nobody can help with that this will work as a temporary fix.
polyisoprene
Posted 2016-08-06T19:03:45.990
Reputation: 13
It's definitely not a good idea to do it with worksheet functions, you need to use macros. – Máté Juhász – 2016-08-06T19:15:56.293
Never worked with macros before so I really don't know where to start with that, but I did find a partial solution (works in Google Sheets only) and have edited my question to include it. – polyisoprene – 2016-08-07T01:25:45.587
You need to delete the tag "microsoft-excel" because they don't pertain the your question. You are using Libre Office and Google which don't match the capabilities of MS Excel. – ejbytes – 2016-08-07T02:23:06.907