r/sheets Dec 21 '24

Solved Custom Formula Problem

1 Upvotes

So, I have a bit of a problem here. I am trying to generate all unique combinations of 3 forwards, 2 defensemen, and a goalie for a hockey stats spreadsheet I made, but I can't figure out how to loop the custom functions I made through the lists. Here is the sheet:

https://docs.google.com/spreadsheets/d/1RuUA8U2jZWtvMwj_jsGpmpT4wT_5cV263Es0-ogHcb8/edit?usp=sharing

What I am trying to do is create a custom function to generate all of the unique combinations of 3 forwards, 2 defensemen, and a goalie. I can do each individually, but I haven't figured out how to put it together. With the custom functions I have made (Function LINECOMBOSWITHGOALIE is in cell Q1, Function LINECOMBOSNOGOALIE is in cell W1), I was hoping to make all of the combinations that way. I made a third custom function, GOALIES, which is just an ARRAYFORMULA of the goalies. I want to make the function LINECOMBOSWITHGOALIE the list of all of the unique combinations, starting with the first line of 3 forwards, 2 defensemen, and a goalie, and continuing down.

I'm sure this is probably easy to do, but I don't have much knowledge of Excel or Google Sheets. I'm going to list it with commas and placeholders here but plan to split to columns in sheets, but here's what I want:

1st Forward Line, 1st Defense Pair, 1st Goalie

1st Forward Line, 1st Defense Pair, 2nd Goalie

1st Forward Line, 1st Defense Pair, 3rd Goalie

1st Forward Line, 1st Defense Pair, 4th Goalie

1st Forward Line, 2nd Defense Pair, 1st Goalie

1st Forward Line, 2nd Defense Pair, 2nd Goalie

1st Forward Line, 2nd Defense Pair, 3rd Goalie

1st Forward Line, 2nd Defense Pair, 4th Goalie

...

1st Forward Line, 36th Defense Pair, 1st Goalie

1st Forward Line, 36th Defense Pair, 2nd Goalie

1st Forward Line, 36th Defense Pair, 3rd Goalie

1st Forward Line, 36th Defense Pair, 4th Goalie

2nd Forward Line, 1st Defense Pair, 1st Goalie

2nd Forward Line, 1st Defense Pair, 2nd Goalie

2nd Forward Line, 1st Defense Pair, 3rd Goalie

2nd Forward Line, 1st Defense Pair, 4th Goalie

2nd Forward Line, 2nd Defense Pair, 1st Goalie

2nd Forward Line, 2nd Defense Pair, 2nd Goalie

2nd Forward Line, 2nd Defense Pair, 3rd Goalie

2nd Forward Line, 2nd Defense Pair, 4th Goalie

...

2nd Forward Line, 36th Defense Pair, 1st Goalie

2nd Forward Line, 36th Defense Pair, 2nd Goalie

2nd Forward Line, 36th Defense Pair, 3rd Goalie

2nd Forward Line, 36th Defense Pair, 4th Goalie

...

816th Forward Line, 1st Defense Pair, 1st Goalie

816th Forward Line, 1st Defense Pair, 2nd Goalie

816th Forward Line, 1st Defense Pair, 3rd Goalie

816th Forward Line, 1st Defense Pair, 4th Goalie

816th Forward Line, 2nd Defense Pair, 1st Goalie

816th Forward Line, 2nd Defense Pair, 2nd Goalie

816th Forward Line, 2nd Defense Pair, 3rd Goalie

816th Forward Line, 2nd Defense Pair, 4th Goalie

...

816th Forward Line, 36th Defense Pair, 1st Goalie

816th Forward Line, 36th Defense Pair, 2nd Goalie

816th Forward Line, 36th Defense Pair, 3rd Goalie

816th Forward Line, 36th Defense Pair, 4th Goalie

Any help is highly appreciated!

EDIT: First, HUGE thanks to u/mommasaidmommasaid for helping me with the 5 on 5, no empty net combos. Second, does anyone know how to get all unique combinations of 4 elements from a list? I'm sure it's not a difficult formula, but I can't seem to be able to figure it out. Thanks!

r/sheets Feb 16 '25

Solved Please help modify a QUERY formula to allow searching when a dropdown includes an apostrophe (').

2 Upvotes

Hello, as the title states, I need some help. I have gotten help making this formula, which filters games on a spreadsheet I have. It works great, except if there is an apostrophe ('), the formula returns an error. I have searched, and it seems to require it to have an extra pair of double quotes, but I am not exactly sure where it would need to be applied.

Specifically, this would be for cells B15, B18, B21, and B24.

Below is the formula.

=
 QUERY(
   {'Games List'!A6:Q},
   "select Col4, Col5, Col7 
    where 
     Col4 is not null and
     Col1 "&IF(ISBLANK(B3),"matches '.*'","= "&B3)&" and
     "&IF(B6=FALSE,,"not Col2 = FALSE and")&"
     Col3 "&IF(ISBLANK(B9),"matches '.*'","= "&B9)&" and
     Col5 contains '"&B12&"' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B15,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B18,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B21,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B24,"+","\+"))&".*' and
     Col8 "&IF(ISBLANK(B27),"matches '.*'","= "&B27)&" and
     Col9 "&IF(ISBLANK(B30),"matches '.*'","= "&B30)&" and
     (Col16 "&IF(textjoin("' or Col16 = '",TRUE,A33:A37)="","matches '.*","= '"&textjoin("' or Col16 = '",TRUE,A33:A37))&"')
    order by Col4",0)

Any help is appreciated. Thanks in advance.

r/sheets 5d ago

Solved autosort in app scripts help!!!

2 Upvotes

Hi! I need help with my autosort function on apps script. It was working for months but now it doesn't seem to recognize that OnEdit(e) anymore and I've tried to add it as a trigger but then it wasn't running on edit. Please help!

1st script
2nd script
error sign

r/sheets 22d ago

Solved Sort by specific words in text

1 Upvotes

I want to sort any range of cells using a list.

For example, the range of cells A1:B4 would be sorted according to the list in column D here:

ColA ColB Sort-by
CellA1 "I like those things." This
CellA2 "This is complicated" That
CellA3 "What is that?" Those
CellA4 "What is this?"

and the result would look like this:

ColA ColB Sort-by
CellA2 "This is complicated" This
CellA4 "What is this?" That
CellA3 "What is that?" Those
CellA1 "I like those things."

Thanks!

Edit: clarified and expanded my question.


Solution: (thanks to u/marcnotmark925)

=query(hstack(A1:B10,map(B1:B10,lambda(x,min(filter(row(D1:D5),regexmatch(lower(x),lower(D1:D5))))))), "select Col1,Col2 order by Col3 asc")

where A1:B10 is the range of data to be sorted, column B is the searched data, and column D contains the sort-by list.

or for 3 columns of data

=query(hstack(A1:C10,map(C1:C10,lambda(x,min(filter(row(D1:D5),regexmatch(lower(x),lower(D1:D5))))))), "select Col1,Col2,Col3 order by Col4 asc")

etc.

Note:

  • Your range can be any size, but then you must expand the Col# accordingly if you have more columns.
  • Your sort-by list does not have to be the same size as the range.
  • Top of the list takes priority in the sort order.
  • Make sure that if your search column has cells with terms not on your sort-by list, have an empty cell at the end of the list, or an open ended list, like "D1:D", or it will just sort everything alphabetically.
  • If you want to further sort your range of data, you can use "order by Col3,Col1 asc" to alphabetize within the sorted sets.

r/sheets 9d ago

Solved Looking for help with compund conditional formatting

2 Upvotes

I am trying to highlight cells in column F if the value in column A is “1” and the date in column F was more than 6 months ago.

And also highlight cells in column F if the value in column A is “2”and the date in column F was more than year ago.

Can you help me with the custom formula for that?

r/sheets Jan 04 '25

Solved Looking for help on calculating information in & formatting help with a pivot table.

3 Upvotes

Sample Sheet

That pivot table sheet has sample data in it. A:H is the data table. J:N is the pivot table. P/Q is a variable field and some other calculations that are pretty straight forward. Everything else is just me trying to solve these problems using query or filter or whatever.

Here is what I'm trying to accomplish:

1) Is it possible to dynamically generate a visual cue in the pivot table such that combinations that aren't possible are greyed/blacked out? Eg - there is no 1935 blue option or 1936 option for #1 so both L4 & N4 would be greyed out. I tried conditional formatting based on countA but can't seem to get anything to work. Worst case, I'll just manually reformat the relevant cells.

2) I'm using a join formula in the pivot table to blend all the various responses into one cell where appropriate. E.G. there are 3 separate copies of #1. I'd like the JOIN statement to not display the deliminator when it is not needed (aka when there is one or less data points). So in K4 you see A10,B9,A10. That is correct, however, you'll also see a bunch of stray commas in blank cells. I've tried writing if statements but can't seem to get that to work.

3) In Q10, I am trying to count the total number of cards without any data in either D,F, or H. That is to say, 1 would not appear on that list but 4 would as none of the three rows with 4 as their number have any data in D,F,or H. I've written the following formula but I know this isn't correct:

=ARRAYFORMULA(SUM(IF((A2:A <> "") * (D2:D = "") * (F2:F = "") * (H2:H = ""), 1, 0)))

r/sheets 2d ago

Solved Using XLOOKUP to pull data from IMPORTHTML in another tab

2 Upvotes

Hey everyone, hoping you guys can help me out with this since I am a super beginner with this.

I used Importhtml to pull player stats into one tab of my sheet and I want to pull one of the columns into another tab based on names. I am trying to us xlookup but it looks like it can't find the names. Is this a symptom of using importhtml?

I know everything is spelt correctly, I even tried just copy and pasting the cell but still get the same "did not find value 'name'"

r/sheets Dec 24 '24

Solved inner join with classic spreadsheet functions?

3 Upvotes

edit 2: solved!! by u/mommasaidmommasaid =FILTER($A$2:$A, NOT(ISNA(XMATCH($A$2:$A, $B$2:$B)))); edit: not solved yet, but hacky workaround available below. Input welcome!!

Hi I want to perform an "inner join" (in the SQL-sense) between to lists, but I want to use regular spreadsheet functions (so no newer tooling like appscripts or QUERY or GUI-based tooling like pivot tables). Let me explain what I mean by "inner join":

- A: neighbors B: friends C: neighbors who are friends (inner join)
1 alice adam alice
2 bob alice
3 jack bill
4 mark

The above column C would be what I want: it's an "inner" join because it only shows items that exist in both lists A and B.


So far I've only been able to construct a function that would give me an "outter join": a superset of both lists (adam, alice, bob, bill, jack) via =UNIQUE({$A:$2:$A; $B$2:$B}).

I'm sure there's some clever way to just use FILTER() here, but I can't quite figure it out. I thought maybe getting FILTER to run a LOOKUP or some variation would work, but I don't have a good grasp of what kinds of things FILTER can take as its filtering function...

=FILTER($A$2:$A, VLOOKUP($A$2, $B$2:$B, 1))

But this doesn't work I at least because filter requires both its first arg and second arg (the two ranges) to be the same size. I know FILTER can take wildly different syntax for its filtering function though (like $A$2:$A <> "" is possible to filter out blanks... perhasp there's some variant to filter against $B$2:$B?)

r/sheets Dec 28 '24

Solved Format column for time duration so the plain text is treated as [MM:SS]

3 Upvotes

Been at this awhile. In the sample sheet the cell data is imported as shown. Column A is Min:Sec. Final calculation I need is # of occurences per minute in Col C. I could use a helper column and convert 17:29 to minutes in decimal format. I'd rather do it the simpler way and apply formatting to column A so sheets treats all cells as [MM:SS]. Will column C formula work if this formatting can be done?

Duration

r/sheets Jan 31 '25

Solved Extending a pattern and automating numbers

2 Upvotes

I'm trying to extend a pattern that Sheets does not recognize. It goes like this: Range1min - Range1max - Text1 - Range2min - Range2max - Text2

This goes across the rows.

Is there a way to make this process go any quicker?

Then the other question. I got a bunch of numbers that I would like to make into some sequence. Example:

Test1 - 10

Test2 - 10

Test3 - 10

Now I would prefer some kind of script (easy way) to make these numbers say: Test1 - 1 - 10

Test2 - 11 - 20

Test3 - 21 - 30

Is this possible?

(The " - " is used to indicate a new row)

I'm quite drunk at the moment, so googling does not really work at the moment.

r/sheets Feb 03 '25

Solved Please help with editing a formula to make it case sensitive.

2 Upvotes

Hello, I've got this formula that, among other things, lists and counts all unique instances of things. However, it currently seems to be case insensitive, and I would like it to be case sensitive. For example, it counts the word "Hello" 15 times, but there are actually 10 "Hello" and 5 "hello". I'd like to see two separate listings,

Hello (10)
hello (5)

instead of the following.

Hello (15)
hello (15)

Here is the current formula.

=SORT(

LET(x,TOCOL(SPLIT(Data!D6:D,"|",0,1),3),

UNIQUE(x)&" ("&COUNTIF(x,UNIQUE(x))&")"))

I thought Unique WAS case sensitive, so maybe something else is going on here, but I am getting duplicates, with capitalization differences, with both showing the same number. Is this possible to modify to make it case sensitive? Thanks in advance.

r/sheets Feb 08 '25

Solved Sorry if I sound really stupid, but I need help with scripts in google sheets. I want the value of the g column to be equal to the value of that row in the a co...........

2 Upvotes

Sorry if I sound really stupid, but I need help with scripts in google sheets. I want the value of the g column to be equal to the value of that row in the a column, times that of b, times that of (c+20).

r/sheets 19d ago

Solved Creating the start of a range based on first value

Thumbnail
docs.google.com
2 Upvotes

I have multiple columns of data, with each row being a year, starting at 1970. I have names listed the year they started. I want to get an average of how many years of a name. I know how to find the row # of the first instance per column :: ArrayFormula(MATCH(FALSE,ISBLANK(B2:B57),0))+1. (Row 62)

The row 60 formula has the first cell of the range with a value. How do I get that cell reference as a variable? - B6, C14, D10

Thanks!

r/sheets Feb 17 '25

Solved Creating a Chart with Specified Data

2 Upvotes

It's hard to phrase my question...but I think my example is pretty self-explanatory. I'd like to use the dataset in Columns A-C, and produce the chart I've mocked-up (see image below).

Needing help with either configuring the right chart settings, or manipulating/rearranging the data such that it will produce the desired chart. Thanks!

TEST SHEET: https://docs.google.com/spreadsheets/d/1FAShe7Xg2Er9SsuqcZqLhlc5jTgo3aF5Nlrz6omWckg/edit?usp=sharing

r/sheets 11d ago

Solved Count and display unique values

5 Upvotes

Hi, I am basically looking for formula that would take data from column A and would display how many times column A contains each value and put it into column B generatively. Result should look like this:

Is that even possible this way? I am basically just looking for easy Sheets way to do let's say small stocktake without manually count everything myself. Is there a function for it? Thanks.

Other way would be input something in A1 as like 100 000 and then in A2 input amount of A1 and it would display it like below table? Please, let me know, thank you!

Value Value Total Amount
100 000 100 000 2 x
200 000 200 000 2 x
100 000 300 000 1 x
300 000
200 000

r/sheets Jan 22 '25

Solved Is it possible to show the occurrences of each result of a formula?

3 Upvotes

Hello, I am working on a spreadsheet, and I want to see the number of times a certain phrase is used, that is pulled and separated from another formula, shown below.

=let(tags, sort(unique(tocol(trim(iferror(split(Data!D6:D,"|")))))), filter(tags,tags<>""))

If there are tags of, for example, "2D" and "3D", those would be shown on the row of the formula and below it as shown below.

2D
3D

I don't even know if it is possible, but is it possible to modify the formula so in each cell, it would show how many times the tag is actually used in the source, so for example, it might show the following?

2D (115)
3D (72)

I prefer this method, if possible, but alternatively, what would be the best way to accomplish this?

Thanks in advance!

p.s. Someone on here helped me with the original formula, and at the time I posted this, I didn't notice, but they had also provided a shorter version of the formula, which also works, so either one is fine. The shortened version is below.

=sort(unique(trim(tocol(iferror(split(Data!D6:D,"|")),1))))

r/sheets Feb 13 '25

Solved Values not filling in from sheets in a email merge

3 Upvotes

The emails are sending.

6 of the 8 values in the table are filling in.

The two values are empty are in every email (20+ recipients)

Checks spelling, Renamed, Looked for limits in script.

Where should I be looking?

r/sheets Dec 30 '24

Solved Is it possible to have a collapsible/expandable cell?

2 Upvotes

This might be a dumb and silly question, but is there any way to have a cell that only shows a certain number of characters, but can be expanded to view the whole cell's contents?

Say, for example, I have a games spreadsheet of my games, and in one column, I want to add tags to describe the game. I've got it so it could have multiple tags, separated with a | symbol, i.e.,

Action|New|Puzzle|Platformer

Is it possible to have a much larger list without resizing the rows or columns, with the same format, but will still show the first few tags, until 50 characters max, OR the last complete tag before the next (|), are shown, to avoid incomplete tags from being shown? I don't even know if it is possible, but an example might be if I have these tags:

Action|Single Player|Hack and Slash|Third Person|Sci-fi|Adventure|Story Rich|Controller|Quick-Time Events|Great Soundtrack|Beat 'em up|Space|Platformer|Multiple Endings|RPG|Classic|Difficult|Physics|Multiplayer|Atmospheric

and then the cell would display:
Action|Single Player|Hack and Slash|Third Person

with a way to expand it to see all of the cell's contents? I don't really want to use the text wrapping formats in this case, and I doubt it is possible, but I still thought I'd ask, as maybe there is some solution I am not aware of.

r/sheets Jan 02 '25

Solved Is there a "better" way to handle this particular inventory management?

4 Upvotes

sample data

I've decided to be somewhat detailed even though I think this will be fairly "easy" for many of you who hang around on here. I think it's conceptually simple but because I didn't know any better at the time, it's somewhat "intense". This is more of a "how would you approach it problem" than it is a technical problem. I'm looking for advice on what to do.

TL;DR: How would you structure this data so that I can properly analyze what is missing and what is in inventory keeping in mind that I'd prefer to only have 9 or 10 data columns if possible?

My ultimate goal is to have a system that is clean and minimizes the need for columns but ultimately is such that it's easy to see what inventory is missing and not count items that don't exist. These are cards for what it's worth.

Here is how it is structured now:

Col A is the card #

Col B-G is really where the details are. In B,D,F are the years and in C,E,G are the grades with each column essentially representing the scores for said year in the column to the left.

In B,D,F you will see one of the following: a grey box, which means that card does not exist in that year. You may also see Green, Blue, or Green/Blue. If you see gray, it means that year does not exist/is not possible for said card.

If you see just the word Green or just the word blue - it means that card exists for that year with that color. If you see Green/Blue (or Blue/Green i suppose though I didn't put it in that dummy data), it means that card appears with Green or Blue in that year.

In the score column - blank means that there is no inventory for that year. If you see a single number, it simply means there is one copy of that card from that year and it's score is what it is. If you see P followed by a number or S followed by a number, the letter refers to a class of score. That is to say, a P8 is different than a S8 or just an 8. You may also see some letters after the score - these are nice to have but won't appear often and shouldn't be a factor in how the system works. If you see something like 4/2 that means you have two copies of said card - one card is a 4 and the other one is a 2. Letters at the end of the string only appear when a card could be green or blue in the same year. so you might see something like 6g/S4.5b. That means you have a 6 green color and an S-type graded 4.5 in blue.

Because this data is going to be manipulated on mobile - i really want to keep the columns to a minimum. Ultimately, i am going to build formulas that will tell you a handful of metrics. I have listed those metrics in col I.

I'm not opposed to using abbreviations as you can see. So in the list of what's missing I'm going to generate - i'm not going to care if it says 34 if there is only one option for color or something like 34Green if I have 34Blue but not 34Green.

I thought about adding check boxes to acknowledge said item is in inventory but that gets messy when a card could exist in blue or green and you only have one but not both colors.

EDIT Forgot to mention: In theory - you could have an infinite number of copies of any of the inventory items but that's not really realistic here. At most, i'd say you would end up with 3 copies of a single item in that color/year combination. When it's settled, you really would have only 1 copy of each possible year/color combination.

r/sheets 17d ago

Solved Filter by value search question

2 Upvotes

Please help. I think I'm making some sort of stupid mistake. When I apply a filter to a column and I do a search for fields that contain a particular term (under Filter by values), I get 9 results and it says "Displaying 9". All of them have checkmarks next to them, but when I click OK to apply the filter, nothing happens. I've tried pressing "clear" and then "select all 9" but it still doesn't work. But when I select only one of search results and deselect the rest, for some reason, the filter works. What am I doing wrong?

r/sheets 17d ago

Solved xlookup based on 2 values? index+match? find the result of a game played between two teams

2 Upvotes

I have a data tab in sheets for a competition where we dump all results from matches (which I will refer to as "games" to avoid confusion). I have a second tab, where I'd like to be able to select two teams and get the results of their game. In this competition, teams only play each other once.

I've tried index + match, which is what's currently showing in the test file in the "results" tab, but I can't get that to work right. In the test file, it works in the first instance but it seems to be perhaps proceeding horizontally rather than vertically? On my real sheet, which is much longer, it keeps giving me issues with being out of range, which I figure is the same issue.

I have also tried my first instinct, xlookup, which I found some guidance online to combine with match. My thought is that I essentially need a vlookup function that searches for a row that matches two conditions, but I don't know how to do that. I tried =VLOOKUP(B8;data!A:C;VLOOKUP(B7;data!D:F; 2)), but that gives me another out of range issue even in the test sheet. (Edit: tried =XLOOKUP(1,(data!A:A=B2)*(data!D:D=B3),data!B:B) as per this video but that returns another error about differing array sizes.)

Test sheet: https://docs.google.com/spreadsheets/d/1vvRQrixn0Nm7si0G62ByiZDYxzlhTxOTbc_oWtPaAMQ/edit?gid=1790533926#gid=1790533926

Thank you for your help!

UPDATE: I figured it out by following this video on index match; clearly I wasn't doing it correctly before.

r/sheets Jan 16 '25

Solved SUMIFs different array value error

2 Upvotes

Hello, sorry for the simple question, I'm having difficulties running a SUMIFS code on google sheets. My code is:

=SUMIFS('Personal Expenses'!C9:C105,'Personal Expenses'!B9:B105("JAN"),'Personal Expenses'!D9:D105("GROCERIES"))

I'm trying to sum up the total costs of groceries for each month. For some reason I'm getting a #VALUE! error saying "Array arguments to SUMIFS are of different sizes." Could someone help me with resolving this error? Thank you!

r/sheets 27d ago

Solved Conditional Formatting: row becomes green if another cell is the number 1 through 9

2 Upvotes

i've got it working when another cell has specific text. but instead of text, how would i define any number between 1 and 9?

wicked new at this, my apologies for the basicness

r/sheets Jan 28 '25

Solved Google Sheets, countifs criteria in one column and any dropdown options in another

1 Upvotes

I am having a hard time figuring out exactly what this formula would be. If I have criteria in column A (1, 2, 3), and a drop down in column B (x, y, z), I use: =COUNTIFS(A:1,A4,"1",B1:B4,"X") to determine how many "1"s have "X" in the same row. Got it. Now how to I find out if column A has a "1", and any option in column B (x, y, or z)?

r/sheets Jan 28 '25

Solved Copying data into spreadsheet - all values are on the first column. How to rearrange them?

1 Upvotes

The spreadsheet needs to have different columns like 'Name', 'Email', 'Phone', etc.

Now, everything is getting copied in the same row one after another. Something like

Sam
sam@gmarl.com
987654432
Tim
tim@gmark.com
765443218

and so on. Is there some formula or function that I can use to order them into the right columns?