b2 | Bertini 2.0 : The redevelopment of Bertini in C++ | Math library

I have two google sheets in the same workbook and I am trying to import certain columns from one into the other sheet based on what hour of class was chosen. For example, I have the Date in column A, Name in B, Email in C, and Number in D and other stuff, then the Class in I.

Column A - Date Column B - Name Column C - Email Column D - Number ... Column I - Class

My goal is to import column B through D if column I has a certain class. I tried a using this if statemnt: =if('Confirmação'!I2 = A1,(=importrange("sheet_url","Confirmação!B2:D2")), "NOPE") where A1 has the name of the class to look for, but it resulted in a #Error.

Then I tried a variety of query such as the following: =QUERY({'Confirmação'!B2:D2,'Confirmação'!I2},"Where I = 'Terça 19h English 1'") =QUERY({'Confirmação'!B2:D2, 'Confirmação'!I2},"Where 'Confirmação'!I = 'Terça 19h English 1'")

and also added the IfError: =iferror(QUERY('Confirmação'!B4:I4,"Where I = 'Terça 19h English 1'"),"Vaga")

Could someone correct my functions or help with a google script? Thank you! Very much appreciated! (Sorry about the Portugues-it's a project I am working on in Brazil)

I have this situation on google sheets

image

I wanted to make a monthly report with sumifs with 2 conditions like the image above. But all I got was errors.

Code I am using

=sumifs(D2:D13;D1:G1;C16;B2:B13;or(B2:B13="apple","cherry","watermelon"))

I am trying to divide merged information from one cell into separate cells.

one cell:

divided data: (I want to export each part into another cell)

I can't simply divide by finding empty space, status cell which is case-sensitive | status:WBB NAS MRR OWA PXA| has a different data range with spaces that can't be divided.

Split ( expression [,delimiter] [,limit] [,compare] )

I need to generate a list of dates in a dataframe by days and that each day is a row in the new dataframe, taking into account the start date and the end date of each record.

Input Dataframe:

Expected Output:

I have the following Sets:

So the dictionary I got is as below:

this code returns me a black screen, what am I doing wrong?

python file:

I use

=trim(cell)<>""

to check if the cell is not blank/null or white space.

I want to count how many row from row1 to row100 where there is no blank or null or any white space(s) in the column A or B. So it will count only if both column A and B has value (not blank/null/space).

When using countifs() to count item using multiple criteria. I can't use trim() as the parameter. For example :

I wanted a ArrayFormula at C1 which gives the required result as shown.

Entry sheet:
(Column C is my required column)

Date Entered is the date when the Name is Assigned a group i.e. a, b, c, d, e, f

Criteria:

1. The value of count is purely on basis of Date Entered (if john is assigned a on lowest date(10-Jun) then count value is 1, if rose is assigned a on 2nd lowest date(17-Jun) then count value is 2).
2. The value of count does not change even when the data is sorted in any manner because Date Entered column values is always permanent & does not change.
3. New entry date could be any date not necessarily highest date (If a new entry with name Rydu is assigned a on 9-Jun then the it's count value will become 1, then john's (10-Jun) will become 2 and so on)

Example:

After I sort the data in any random order say like this:

Random ordered sheet:
(Count value remains permanent)

And when I do New entries in between (Row 4th & 14th) and after last row (Row 17th):

Random Ordered sheet:
(Doesn't matter where I do)

1. ={"AF Formula1"; ArrayFormula(IF(B2:B="", "", COUNTIFS(B$2:B, "="&B2:B, D$2:D, <"&D2:D)+1))}
2. ={"AF Formula2";INDEX(IFERROR(1/(1/COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)))))}

I tried to figure my own ArrayFormula but it's not working:

I got Formula for each cell:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
I figured out Filter doesn't work with ArrayFormula so I had to take a different approach.

I took help from my previous question answer (Arrayformula at H3) which was similar since in both cases each cell FILTER formula returns more than 1 value. (It was actually answered by player0)

Using the same technique I came up with this Formula which works absolutely fine :

=RANK($D2, ARRAYFORMULA(TRANSPOSE(SPLIT(VLOOKUP($B2, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ","))), 1)

Now when I tried converting it to ArrayFormula: ($D2 to $D2:$D & $B2 to $B2:$B)

=ARRAYFORMULA(RANK($D2:$D,TRANSPOSE(SPLIT(VLOOKUP($B2:$B, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ",")), 1))

It gives me an error "Did not find value '' in VLOOKUP evaluation", I figured out that the problem is only in VLOOKUP when I change $B2 to $B2:$B.

I'm sure VLOOKUP works with ArrayFormula, I fail to understand where my formula is going wrong! Please help me correct my ArrayFormula.

Here is the editable sheet link