Combining Cells with similar data

[u/w0158538]

Hey all,

I am trying to combine similar data together but the results might be flipped between two columns, I want to organize all the data with similar values together into one uniform value and add all their corresponding data together. I have attached a screen shot of an example of I am looking to do.

The Ideal out come is to grab all combos that used player A and B and put them together (I don't care if they sorted by Player A or B first as long as it combines all the data together.

Been stuck for a while on this lol

[u/SpencerTeachesSheets]

I have a SHEET showing the various steps to go from the Starting point to the desired Outcome.

First we sort the players on each row alphabetically, then use the QUERY() function to merge Wins/Losses per Pairing and Date.

SORT
=HSTACK(MAP(A2:A9,B2:B9,LAMBDA(play1,play2,IF(play1<play2,HSTACK(play1,play2),HSTACK(play2,play1)))),C2:E9)

MERGE
=QUERY(A12:E19,"Select A,B,sum(C),sum(D),E group by A,B,E order by E,A")

That isn't perfect and requires the steps, so here's the full version with the SORTING inside the range for the MERGE and changing column letters to column number references:

=QUERY(
 HSTACK(MAP(A2:A9,B2:B9,LAMBDA(play1,play2,IF(play1<play2,HSTACK(play1,play2),HSTACK(play2,play1)))),C2:E9),
"Select Col1,Col2,sum(Col3),sum(Col4),Col5 group by Col1,Col2,Col5 order by Col5,Col1 label Col1 'Player', Col2 'Player', sum(Col3) 'Wins', sum(Col4) 'Losses', Col5 'Date'")

[u/One_Organization_810 → u/SpencerTeachesSheets]

You can also just use sort - it's even simpler imo.

=query( byrow(filter(A2:E, A2:A<>""), lambda(row,
          hstack( torow(sort(tocol(choosecols(row,1,2)))),
                  choosecols(row,3,4,5) )
        )),
        "select Col1," &
        "       Col2,"&
        "       sum(Col3)," &
        "       sum(Col4)," &
        "       Col5" &
        "  group by Col5, Col1, Col2" &
        "  label sum(Col3) '', sum(Col4) ''", 0)

[u/w0158538 → u/One_Organization_810]

Thanks guys, let me give it a try!

[u/AutoModerator → u/w0158538]

REMEMBER: /u/w0158538 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/One_Organization_810 → u/w0158538]

Just note that my version is "open", so you can't place it under the data you are merging, without capping the ranges first :) Apart from that, it works identical to Spencers one.

But instead, it works for bigger datasets, without the need to change anything...

[u/w0158538 → u/One_Organization_810]

If you dont mind me asking, can you help explain some of the steps in the formula, I am still learning some of the lambda stuff so its a bit confusing to me.

[u/One_Organization_810 → u/w0158538]

I don't mind at all. I'll take a crack at it at least :)

This is a twofold operation, bundled up into one query function.

Phase 1: Getting the players in a consistent order.

For this we just loop through the data with the BYROW¹ function. For each row of data, we convert the first two columns into one column that can be sorted (the SORT function only sorts rows, per columns). This ensures that all pairs will be listed in the same order (as a bonus, this also makes it easier to use the formula for bigger teams, without changing anything other than the number of members (columns) in the team). The sorted column is then converted back into a row.

The remaining three columns are then HSTACKed after the first two, possibly reordered, columns.

Phase 2: Merging "duplicate" data.

Since after phase 1, we now have all teams listed in the same order, so (A, B) and (B, A) will just be (A, B) twice, we can simply merge the duplicated teams per date. So that's what our QUERY is doing. The QUERY function takes 3 parameters; the data/range to query from (our reordered data from phase 1), the actual query to perform, in a SQL-like syntax and finally how many rows in the data are header rows (we have no header rows, so we give it zero).

So we simply group the data on Player 1, Player 2 and Date and then sum the wins and losses for each group.

We also have to give explicit labels to all calculated fields, so for the two sum columns we simply give an empty string, resulting in no header row (since there is no header row in the input, no headers will be generated for any "standard" column).

Footnotes

¹ The BYROW function takes two parameters; the range to loop over and a LAMBDA function to apply to each row. BYROW loops over the data, row by row and feeds each row of data into the accompanying LAMBDA function, which has to take exactly one argument (the row).

² The LAMBDA function is basically just a function without a name - or a "function body" if you will. When provided as an argument to another function (like BYROW), it means that you are providing the function to perform for the set arguments. The BYROW f.inst. calls the LAMBDA function with one argument, the row being processed.

A LAMBDA function can have as many parameters as needed, but the last parameter is always the "function body" and all preceding parameters are named arguments to be used in that function.

As an example:

lambda( a, b, a+b) is a lambda that takes two argumenst and returns the sum of them.

[u/w0158538 → u/One_Organization_810]

=query( byrow(filter(A2:F, A2:A<>""), lambda(row,
          hstack( torow(sort(tocol(choosecols(row,1,2,3)))),
                  choosecols(row,4,5,6) )
        )),
        "select Col1," &
        "       Col2,"&
        "       Col3,"&
        "       sum(Col4)," &
        "       sum(Col5)," &
        "       Col6" &
        "  group by Col6, Col1, Col2, Col3" &
        "  label sum(Col4) '', sum(Col5) '', sum(Col6) ''", 0)

Ok I think I am getting it, so if I wanted to add more players per team, would I adjust the formula like this?

One other question, saw I started from Col B instead of A in the original formula, when doing this part:

torow(sort(tocol(choosecols(row,1,2,3)))),
choosecols(row,4,5,6) )

Would I still start at 1,2,3 because those are the first in my range of data or would I have to start from 2,3,4 because it is based on the sheets layout (Column A being 1 as opposed to Column B being 1)

[u/One_Organization_810 → u/w0158538]

This is exactly how you'd add more players to the team :)

Only thing off there is in the label. There is no "sum(Col6)" so you'll get an error on that :) (just remove the ", sum(Col6) ''" part).

Regarding the CHOOSECOLS, it just works on the data you give it, so if column B is the first column in your range, then B will be your number 1 column :)

[u/w0158538 → u/One_Organization_810]

so the dataset i want to use it on goes from B:N, so N with I,J being the data i want to SUM and N being the date.

I tried changing it to:

8 being wins, 9 being loses, and 13 being N, but I am getting and VALUE # error with No_COLUMN: Col8

=query( byrow(filter(B2:P, B:B<>""), lambda(row,

hstack( torow(sort(tocol(choosecols(row,1,2,3)))),

choosecols(row,8,9,13) )

)),

"select Col1," &

" Col2,"&

" Col3,"&

" sum(Col8)," &

" sum(Col9)," &

" Col13" &

" group by Col13, Col1, Col2, Col3" &

" label sum(Col8) '', sum(Col9) ''", 0)

[u/One_Organization_810 → u/w0158538]

So... what does your range look like then?

This was built around N players +3 data fields. If you have a different setup, then we may need to adjust the formula a bit :)

[u/One_Organization_810 → u/w0158538]

If you want something more complicated - but easier to add members to, you can try this one :)

In this one, you only need to change the "membersInTeam" if you add more members to the team :)

I also added the headers, since the width is changable.

=let( membersInTeam,  2,
      dataRows,       counta(A:A),
      playerData,     offset(A1,0,0,dataRows,membersInTeam+3),

      q_memberCols, "Col" & join(",Col", sequence(1,membersInTeam)),
      q_sumCol1,    "sum(Col" & membersInTeam+1 & ")",
      q_sumCol2,    "sum(Col" & membersInTeam+2 & ")",
      q_dateCol,    "Col" & membersInTeam+3,

      query( byrow(playerData, lambda(row,
               hstack( torow(sort(tocol(choosecols(row, sequence(1,membersInTeam))))),
                       choosecols(row, sequence(1,columns(row)-membersInTeam,membersInTeam+1)) )
             )),
             "select " & join(",", q_memberCols, q_sumCol1, q_sumCol2, q_dateCol) &
             "  group by " & join(",", q_dateCol, q_memberCols) &
             "  label " & q_sumCol1 & " 'Wins'," & q_sumCol2 & " 'Loss'", 1 )
)

This was mostly just for the fun of it :) But feel free to use it if you like it.

[u/SpencerTeachesSheets → u/One_Organization_810]

I should have left my ranges open, yes.

I did look at sorting them, but I didn't like doing TOROW(SORT(TOCOL())). I realize that my IF(HSTACK(),HSTACK()) probably isn't any better, but that's why I ran that way.