open-ended data sorting

asked 2020-08-22 13:23:47 +0100

Seekr
13 ●1 ●1 ●3

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:

image description

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.

edit retag flag offensive close merge delete

Comments

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 ?

igorlius ( 2020-08-22 16:08:34 +0100 )edit

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.

Seekr ( 2020-08-22 17:35:43 +0100 )edit

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?

Lupp ( 2020-08-23 21:03:33 +0100 )edit

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 ( 2020-08-24 00:53:11 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2020-08-23 03:54:38 +0100

igorlius
1067 ●2 ●13 ●19 http://userfriendly.org/

updated 2020-08-23 19:50:01 +0100

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

image description

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

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!

edit flag offensive delete link

Comments

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!

Seekr ( 2020-08-23 14:26:43 +0100 )edit

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!"

igorlius ( 2020-08-23 16:12:44 +0100 )edit

add a comment

answered 2020-08-24 04:49:52 +0100

newbie-02
594 ●31 ●49 ●57

updated 2020-08-25 14:44:47 +0100

see a very similar question with manual solution there: https://ask.libreoffice.org/en/questi...

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]

edit flag offensive delete link

add a comment

open-ended data sorting

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

open-ended data sorting edit

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

open-ended data sorting