Open-ended data sorting

[Seekr]

OS: OpenSuse Leap 15.1

LibreOffice Version: 6.4.4.2

File Format: .ods

I made an account here specifically to ask this question, and I apologize if it’s actually a ridiculous question–basically, there’s something very specific I want to try to set up in this spreadsheet, but I’m not 100% sure it’s even possible to do how I’m imagining.

I want an easy utility to help me organize these online raffles I help manage. I want to be able to sort by prize, or by winner, but without needing to format all the winners in advance. There’s a static number of prizes that are the same every time, while participants tend to drop in and out depending on availability, which makes maintaining a list of winners really unwieldy. The prizes are basically the only static data, with the participants being the open-ended variables that are vexing me.

There’s the basic spreadsheet where I collect data, organized by prize, like so:

That’s the easiest way to keep track of who’s getting what on the fly, since these get pretty fast-paced. But for actually handing out the prizes, it’s easier to track everything by member name (since it’s digital stuff I’m handing out to specific accounts). So maybe on a separate sheet, I want to devise some kind of formula that will look at the entries (the names) and print the column they appear under, e.g:

Name1: Prize1

Name2: Prize1, Prize3

Name3: Prize2, Prize3

And so on. Being able to sort the raw data like this would be super-convenient in larger giveaways, but I can’t seem to find a good way to rig this. I’m asking this here because I’m notoriously bad about overthinking things and I’m sure there’s some very simple solution to this problem that I’m missing. That solution might even be ‘why don’t you just use [x thing] instead’.

Thanks in advance for the advice/metaphorical slap upside the head, and my apologies if I’ve committed some question-asking faux pas here.

[igorlius]

Lets see if i got this right. You manually maintain a table as show in your picture and you would like to generate a table as you described below, which is “automatically” updated as you change something in the manual table correct ?

[Seekr]

It doesn’t necessarily need to be automatic or update in real-time, I’m just wondering if there’s any kind of…I don’t know, filtering system that can do that for me when I need it.

[Lupp]

How to handle the case where one person (NameX) won the same prize more often than once?
In what way id the task actually related to sorting? Is it about sorting the prizes won, about the names, about what else?
Is there an already prepared list of names occurring at least once, or are the names always to be extracted from the three (or more?) columns?

[igorlius]

That is an interessting question, my current solution does not add any quantifier to the persons if they are found multiple times, but that should also be possible todo with another integer array to keep track of the number of accurances of each person for each price column. If any interesst for this extended solution exists, i think i could add it on request.

[igorlius]

Hello,

i think the following macro (see. end of this post) does what you want. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell like this:

=PRIZES2NAMES("A1:C5")

The first argument is the complete table selection (with the prizes in the first row) quoted as a string

The result will be put into one cell and look lke this:

Here the macro module:

Function PRIZES2NAMES(data As String)

	Const sep = ";"
	Dim names as String
	Dim tmp as String
	Dim namesArr() as String
	Dim name as String
	Dim line as String
	Dim out as String
	
	out = ""
	names = ""
	tmp = ""
	line = ""	
	
 	Set oSheet = ThisComponent.CurrentController.ActiveSheet
 	Set oRange = oSheet.getCellRangeByName(data)
 	Set oACell = ThisComponent.CurrentSelection
 	
 	REM collect names 
  	For i = 1 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
            tmp = Trim(oCell.String)
            if ( InStr(names, tmp ) = 0 ) then
           		if ( names = "") then
           			names = tmp
           		else
           			names = names & sep & tmp
           		endif
            endif 
        Next
	Next 
	
	namesArr = Split(names,sep)
	
	REM assign prizes to names
	For k = LBound(namesArr) to UBound(namesArr)
		name = namesArr(k)	
		line = ""
		REM check which process he has won
		
		For j = 0 To oRange.Columns.getCount() - 1
			For i = 1 To oRange.Rows.getCount() - 1
        		Set oCell = oRange.getCellByPosition( j, i )
        		 tmp = Trim(oCell.String)
        		if ( name = tmp ) then
        			tmp = oRange.getCellByPosition( j, 0 ).String
        			if ( line = "" ) then
        				line = tmp
        			else
        				line =  line & sep & tmp
        			endif
        		endif
			Next
		Next
		
		REM if name has prizes add it to output
		if ( line <> "" ) then
			out = name & ":" & line & chr(10) & out
		endif
		
	Next
	PRIZES2NAMES = out
End Function

With a little more effort it would also be possible to insert the values in individual cells, but since i was not sure if that is an requirement i did not add it.

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

[Seekr]

You, sir, are a prince among men. I might see if I can get the values into individual cells, but this gets the job done, thank you very much!

[igorlius]

Glad the solution is sufficient.
If you do implement the indvidiual cell version maybe you can post it here aswell.
It is always nice to have options.

For now, have a nice day and let’s (continue to) “Be excellent to each other!”

[newbie-02]

see a very similar question with manual solution there: Calc - Sorting text from vertical view to horizontal and grouping

added here as the word ‘grouping’ remained me on grouping and subtotals in calc, which both may help to quickly generate nice reports for your or similar requests

[edit]

i wondered about myself why i was so interested in this actually simple topic, and came to a fundamental question:

you can see this - and many other - tasks as ‘composed of subtasks’, in calc many of these subtasks are solvable, but on different ways, partly manually, partly with formulas, partly with macros,

solutions with macros are complicated to learn and use, often ‘unclear’ and therefore error-prone,

(macros and macro language are somewhat ‘aside’ in calc, complicated, not well beloved, not often used, not well supported, and thus stay in an ‘underprivileged’? state, most users and plenty supporters try to avoid them)

solutions with manual support lose the ‘property’ of being ‘dynamic’ (discussed above as ‘update in real time’), after adding new data a new processing of them has to be done,

can someone represent the processing for this request purely in formulas, i.e. so that when applied to other data sets the result appears immediately without any manual intermediate steps?

an example: one part of the OP’s question is that you need a complete sorted list of names without duplicates in one column, therefore 1. the sublists of three columns must be merged - works with textjoin, then 2. this string must be splitted - works with ‘text to columns’, then it must be 3. sorted - ‘sort’ and 4. the duplicates must be removed - advanced filter with ‘no duplicates’,

as far as i know there are no ‘formula solutions’ for 2., 3. and 4. ‘textsplit’, ‘sort’ and ‘filter’ have to be called from the menu,

imho it would be practical if calc - similar to Linux in another area - had formula-solutions for all subtasks, and that theese can be combined in nested formulas,

(it shouldn’t be too difficult as the basic functionality is already programmed for interactive processing and partly for the macro language … ?)

and if i’m on the wrong track because i can’t see the forest for the trees … look at my nickname and be indulgent …

P.S. i’m aware that it’s possible to trigger a macro solution by events and thus achieve a dynamic behaviour, as long as macros in calc are as complicated as they are that’s not a flexible ‘homemade’ solution for ‘simple minded users’

[/edit]