google forms – How to check if the values ​​of two cells exist in two columns of another sheet

I try to make a classroom attendance system where the student saves by submitting her name in Google forms. The final result will be displayed in Google sheets by ticking a box after the name of each student.

So, in Google Sheets, in cell B3 of sheet 2, I would like to write a formula that checks if the text of B2 of sheet 2 (2019-10-20) matches column A of sheet1 where the text contains additional characters (2019-10- 20 04:15:25) AND if the text in A3 of sheet 2 (Robin) exists in column B among the results that have just been matched. If it does – returns TRUE, otherwise returns FALSE.


Select all columns where the cell in the columns contains a word – Google Sheets

I am looking for a query formula that will allow me to select all columns (whole column) containing a specific word in the column.

You can see a test sheet here:

For this example, I would like a query that would scan Sheet1 and select all the columns containing the words "Adam". This would select columns A-D.

Any help would be appreciated.

Thank you.

SharePoint 2013 – Hide and show columns

I am using SP 2013 and currently I am learning SharePoint. Therefore, I will be very grateful to have shown, step by step, how to proceed.

I've created a reward list with a choice column named TypeOfAward (in NewForm, EditForm and DisplayForm) with these choices (NoOfDays, AmtOfMoney, GiftCardAmt) – with the following hidden columns: NoOfDays, AmtOfMoney and GiftCardAmt – My question is the following:

For NewForm, in the TypeOfAward selection column, if users select NoOfDays, the NoOfDays column will be hidden / visible to allow users to enter data and 2 others will be hidden – If users select AmtOfMoney, AmtOfMoney The column will be displayed / visible , and identical for the other choice.

For EditForm, users can edit and edit information based on choices; However, if the choice of TypeOfAward is changed, the original data information for the other 2 columns will be cleared.

For DisplayForm, the display column will be visible depending on the choice of TypeOfAward.

Thank you very much for all your helpers.

Google Sheets Query, filtering by rows and columns?

Is there a way to use QUERY formula in Google Sheets, filter results by row AND columns? Basically, I need to filter the results using two filters. Columns must be filtered by a number between 4 and 10, but their position may vary. So our reference is the value of the first line …. You can not trust the position of a column because it can change ….

For example:

         A          B        C                  D        E  
   1 | -----        5        6                  7        8
   2 | Speed        Slow     Fair               Medium   Fast  
   3 | Comfort      Bad      Barely acceptable  Bla      Bla,bla                
   4 | Reliability  Low      Acceptable         Blabla   Blablabla 

Then something like:

=QUERY('Sheetname'!A1:E; "Select xxx where A = 'Speed')

In the example above, xxx must filter the columns using the numeric value of line 1 ….

SQL Server – Updating Multiple Columns Using the Conditional Case Clause

In the following TSQL code the second column TypeId2 is not updated.
What could be the problem here?

declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 int)
insert into @dd values(25,10, 1),(25,15, 2)
insert into @tt values(25,0,0)

update t set 
t.TypeId1=case when TypeId=1 then number end,
t.TypeId2=case when TypeId=2 then number end
from @tt t inner join @dd d on d.Id=t.Id

select * from @tt;

After running the code above and selecting the data from @tt I get the following result:

Id  TypeId1 TypeId2
25    10      NULL

which is not acceptable because the value of TypeId2 is zero, it should be 15.

google sheets – Query with sums and 2 columns on the left

basically, I have this "problem": I have the query

= Query (Raw_Data! J $ 2: $ BL, "Select J, Sum (U), Sum (V), Sum (W), Sum (X), Sum (Y), Sum (Z), Sum (AA) , Sum (AB), Sum (AC), Sum (AD), Sum (AE), Sum (AF) where J contains "CUS" and (AL = "1-Income") and (AJ = "No CAPEX") or AJ = "unconfirmed CAPEX" and (AH = "No Business") and (AM = "2-Learn") and (M = & # 39;) 2-B2B & # 39;) Group By J ", 0)

It works well, I get almost 90% of what I need BUT, basically, I have another column in the range (P), which I want to see to the right of the J column.

The situation is as follows:

J. P

A . 1
B. 2
C. 2
RE. 3
E. 4
F. 1

I'd like to see the results from the P column to the right of column J (which I already have with the formula above), then the sums (which are already derived from this formula).

How can I add column P?

thank you very much

The application of a function on multiple columns of a failed dataset is missing

Consider a dataset with missing values:

ds={<|"timestamp" -> 
DateObject({2000, 1, 1, 1, 0, 0}, "Instant", "Gregorian", 2.), 
"BASCH" -> 108., "BONAP" -> Missing("Unrecognized", "n/d"), 
"PA18" -> 65., 
"VERS" -> 47.|>, <|"timestamp" -> 
DateObject({2000, 1, 1, 2, 0, 0}, "Instant", "Gregorian", 2.), 
"BASCH" -> 104., "BONAP" -> 60., "PA18" -> 77., 
"VERS" -> 42.|>, <|"timestamp" -> 
 DateObject({2000, 1, 1, 3, 0, 0}, "Instant", "Gregorian", 2.), 
"BASCH" -> 97., "BONAP" -> 58., "PA18" -> 73., 
"VERS" -> 34.|>, <|"timestamp" -> 
DateObject({2000, 1, 1, 4, 0, 0}, "Instant", "Gregorian", 2.), 
"BASCH" -> 77., "BONAP" -> 52., "PA18" -> 57., 
"VERS" -> 29.|>, <|"timestamp" -> 
DateObject({2000, 1, 1, 5, 0, 0}, "Instant", "Gregorian", 2.), 
"BASCH" -> 79., "BONAP" -> 52., "PA18" -> 64., "VERS" -> 28.|>}

I can easily get the average of a given key even with missing values:

no2(Mean, "BONAP")

But if I try to apply Average to 2 columns, the missing values ​​become a problem:

no2(Mean, {"BONAP", "PA18"})

This returns a dataset with missing values. I suspect that this is not the correct syntax, because in the first case, the result is numeric, while the second operation returns a set of data. How to apply a function to multiple columns?


It works:

no2(Mean, #) & /@ {"BASCH", "BONAP", "PA18", "VERS"}

But that's not what I'm looking for. I am looking for a way to do it as part of the dataset.

Edit all columns

I have a database table with over 200 rows (table name: ports).

There is this column (port name). The data in the column are like: aaa 1/15.

I want to edit all the data and change the aaa 1/15 at 1/15.

Of course, the value of the column differs. We end with 1/15 The other with 2/18.

So the point is that I want to get rid of all the aaa in this column for all lines. Is there a way to do it?

javascript – How can I make the last element of this CSS DYNAMIC Columns grid occupy all columns?

Well, I have this container box class which is a CSS Grid container. As you can see, it's dynamic because it uses repeat(auto-fit, minmax(340px, 1fr))

.box-container {
    height: 100vh;
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
    grid-auto-rows: 100px;

.box:nth-child(odd) {
  background-color: lightblue;
  border: 1px solid #000;

.box:nth-child(even) {
  background-color: aquamarine;
  border: 1px solid #000;



By resizing the width of the window, we can arrive at this situation:
enter the description of the image here

I want this last element to occupy two columns instead of one, but I can not define it dynamically.

So here's what I'm waiting for once I get to resize the width of the display window:

enter the description of the image here

But I only know how to do this statically, covering the last child. And that's not what I want because I only want it to extend on both columns at this point of the width of the display window, and the fact of not doing it dynamically would ALWAYS do it between the two columns.

MySQL bulk INSERT .. ON DUPLICATE KEY UPDATE when specifying individual columns

I need to insert thousands of rows in mass if a table of 2 different cronjobs tasks with different datasets, I'm currently using INSERT .. ON DUPLICATE KEY UPDATE

Whereas the first value is the unique key to check duplicates,

For example, the first cron job would be executed (from data source 1):

 ('aaa', 'bbb', NULL),
 ('ccc', NULL, 'ddd')

The second cron job would be launched for example (from data source 2):

 ('aaa', NULL, 'eee'),
 ('ccc', 'ddd', 'fff')

As you can see, the second cronjob overwrites the valid values ​​of the first cronjob with null values ​​that I do not want.
If I withdraw the null the values ​​of the data, the query would break because there would be a data incompatibility.

The only solution I can think of is to run a lot of insert / single updates, which would be slower.

If I could do a lot

 (column1='aaa', column3='eee'),
 (column1='ccc', column2='ddd', column3='fff')

I would not have to worry about the keys, but I do not think it's possible in MySQL. Does anyone know a better way to do it?