0

I have table of contestants and events:

Row Contestant (Col B) Event (Col C)
4 Harold 1500M
5 Alice 1500M
6 Eve 400M
7 Alice 400M
8 Alice 100M
9 Eve 100M
10 David 100M
11 Flynn 100M
12 Greg 1500M
13 Carol 1500M
14 Bob 1500M
15 Carol 400M
16 Harold 400M
17 Irene 400M
18 Irene 1500M

I'd like to create strings from the data:

  • Alice( 100M , 400M , 1500M )
  • Bob( 1500M )
  • Carol( 400M, 1500M )
  • David( 100M )
  • Eve( 100M, 400M )
  • Flynn( 100M )
  • Greg( 1500M )
  • Harold( 400M, 1500M )
  • Irene( 400M, 1500M )

I first tried using "Match", as in "=MATCH($H4,$B:$B, 0)", where H4-H12 is a sorted list of contestants (H4 is Alice, H12 is Irene), but Match would only find the first occurrence of each event. For instance, matching on "Alice" would only return row 5, not rows 5, 7, and 8.

I also attempted the filter function "=FILTER($C$4:$C$18 , $B4:$B18 = $H4, "No Result")", as shown in [https://help.libreoffice.org/latest/en-US/text/scalc/01/func_filter.html][1]. My intent was to convert the array into a string, but I don't get the array syntax. I entered the formula with ctrl-shift-enter, but the cell just shows a "#NAME?" message rather than { 100M, 400M, 1500M } (the events for Alice), which is what I expected. When copied, the filter command doesn't update $H4 in the formula to $H5 as expected, and shows a "Err:508" message. I actually dwelt on this approach for quite a while, but each attempt came back to these two error messages.

I was able to "solve" the problem in a very clunky way as described below, but the solution has real shortcomings.

The "solution" is to create a separate column for each contestant (Alice's header in L3, Bob's in M3, etc.), with formulas =IF($B$4:$B$18=L$3,INDIRECT("C"&CELL("ROW")),"") in each cell from rows 4 to 18 below the contestant's header. Note that when copied across the cells, L$3 will become M$3 in Bob's column, ... and T$3 in Irene's column. Cell("ROW") in the formula references the source data in columns B and C for each contestant (shown in the table above):

Row Alice (Col L) Bob (Col M) ... Irene (Col T
4 =IF($B$4:$B$18=L$3,INDIRECT("C"&CELL("ROW")),"") formula w/ M$3 ... formula w/ T$3
5 formula w/ L$3 formula w/ M$3 ... formula w/ T$3
... ... ... ... ...
18 formula w/ L$3 formula w/ M$3 ... formula w/ T$3

Edit: I was able to create the strings in E22-E31 with the forumulas =CONCAT($L$3, "( ", TEXTJOIN(",", 1, $L$4:$L$18 ) , " )"):

Result from the Clunky Contestant Table solution in columns L through T
=CONCAT($L$3, "( ", TEXTJOIN(",", 1, $L$4:$L$18 ) , " )")
=CONCAT($M$3, "( ", TEXTJOIN(",", 1, $M$4:$M$18 ) , " )")
...
=CONCAT($T$3, "( ", TEXTJOIN(",", 1, $T$4:$T$18 ) , " )")

The main shortcoming is that each time a new contestant is added in columns B and C, there are several manual steps required to update the rest of the sheet. If Eve decided to enter the 1500M, her data would be entered in row 19 in columns B and C. The formulas in L4-T18 would have to be updated to accommodate the new 19th row. If a new contestant John were to enter the meet, a new column U would have to be added.

Another shortcoming is that the events aren't sorted consistently. For example "Alice(1500M, 400M, 100M)" is Alice's result, but "Carol(1500M, 400M)" is Carol's. 1500M and 400M aren't in consistent order. It doesn't matter much which is first, but it matters that they aren't consistent. I tried looking for a SORT formula function, but all of information I found was about how to sort tables, not string segments.

Surely there's a better way to do this, perhaps using the FILTER function. I've attached a screenshot of the spreadsheet. Hopefully that helps understanding. The solution should (but doesn't have to) be compatible with Excel. I'd rather not write a script, but if that's the best approach I can do it in python.

updated screenshot of spreadsheet with three approaches, two failed and one non-scalable

Edit2: The examples are in LibreOffice, not Excel. Appended a simplified screenshot that removes the previous approaches and reflects P.b's suggestion for new formulas in columns D and E.

simplified screenshot, removes previous approaches and adds columns D and E to reflect P.b's recommended formulas

Improve this question
1
  • Your screenshot seems different from your markdown table. Commented Mar 2 at 16:04

2 Answers 2

Reset to default
1

Thank you to P.b for sticking with this question for so long. Given my version of Libreoffice Calc, it looks like Basic code is the best answer. The following gets pretty close.

REM  *****  BASIC  *****
Option Explicit

Sub Main
    Dim currentDoc as Object
    Dim currentSheet as Object
    Dim currentCell as Object
    Dim cellString as String
    Dim keyCell as Object
    Dim keyCellString as String
    Dim dataCell as Object
    Dim dataCellString as String
    Dim accString as String
    Dim resultCell as Object
    Dim i as Integer
    Dim j as Integer
    
    currentDoc = ThisComponent
    currentSheet = currentDoc.Sheets.getByName("Track Events")
    
    For i = 3 to 11 Step 1
        currentCell = currentSheet.getCellByPosition( 3 , i )  'col D row i+1
        accString = ""
        cellString = currentCell.String
        For j = 3 to 17 Step 1
            keyCell = currentSheet.getCellByPosition( 1, j )  'col B row j+1
            keyCellString = keyCell.String
            If keyCellString = cellString Then
                If accString <> "" Then
                    accString = accString & " , "
                End If
                dataCell = currentSheet.getCellByPosition( 2, j )  'col C row j+1
                dataCellString = dataCell.String
                accString = accString & dataCellString
            End If
        Next
        resultCell = currentSheet.getCellByPosition( 4, i )  ' col E row i + 1
        resultCell.String = accString
    Next

I'm going to get an updated version of LibreCalc and try again, or program the script in python.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

For older Excel I'd recommend a helper column to get the unique names, for instance using this formula in D4 dragged down: =IFNA(INDEX(B$4:B$18,MATCH(TRUE,ISNA(MATCH(B$4:B$18,D$3:D3,0)),0)),"")

In E4 refer to D4 like: =IF(LEN(D4),D4&"( "&TEXTJOIN(", ",,IF(B$4:B$18=D4,C$4:C$18,""))&" )","")

Improve this answer

12 Comments

The original didn't include the final step of the clunky solution, which is in E22-E31, so I've edited the post with that information. I've also updated the screenshot to show the formulas alongside the final step. Otherwise, I rechecked the tables and they seem to reflect the screenshot. If the edit and the new screenshot doesn't make the question clearer please be more specific about what's different.
I tried both solutions, and both resulted in #NAME? errors. You'll see that in the new screenshot as well. For good measure I tried to isolate the FILTER command and got the same #NAME? error, which you'll see in S23-31 of the screenshot.
Meaning I tried to isolate the FILTER command from the LET solution by selecting S23-31, typing "=FILTER(C4:C18,B4:B18=m)", and pressing ctrl-shift-enter.
I added a version for older Excel. But you tagged both Excel and Libre Office. What are you using? You can't tag both.
I'm using LibreOffice for this example. It would be helpful, but not necessary, for the solution to be compatible with Excel.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.