In a Google Sheet, I have multiple tables containing a list of dates. I want to create a single (sorted) list containing all the dates and the table they came from.  Table foo: 1.1.19 3.1.19  Table bar: 2.1.19 4.1.19  Result: 1.1.19 foo 2.1.19 bar 3.1.19 foo 4.1.19 bar   Is this somehow possible with regular formulas or do I need to switch to custom functions here?
81191
I have two columns such as:  a   1  b   2  c    and I need to combine them like:  a   1 a   2 b   1 b   2 c   1 c   2   Is it possible with a formula?
15210
I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in another sheet.   I've learned how to QUERY data (thanks to many of you on here) from one sheet to another, but I don't know how to automatically divide 1 row of data into multiple rows.  Here is a  in a visual manner (I will not delete it).   Basically, I have one Google form that collects the following.    Teacher Information (header data) Report Name (header data) Class Information (header data) Student#1 Data (record data: Multiple Form Fields) Student#2 Data (record data: Multiple Form Fields) Student#3 Data (record data: Multiple Form Fields) Student#4 Data (record data: Multiple Form Fields) Student#5 Data (record data: Multiple Form Fields)   I need an automated way to convert that single form response (1 row) into multiple rows (5 rows) each with the same 'header data' but with unique record date.   EXAMPLE Form Data  Header Data  -  Record Date (Student #1)  -  Record Date (Student #2)  -  Record Date (Student #3)  -  Record Date (Student #4)  -  Record Date (Student #5)   EXAMPLE Desired Manipulated Data      Header Data  -  Record Date (Student #1)     Header Data  -  Record Date (Student #2)     Header Data  -  Record Date (Student #3)     Header Data  -  Record Date (Student #4)     Header Data  -  Record Date (Student #5)
95576
Supposing we have a table with four columns (a,b,c,d) of the same data type.  Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
23049
Using standard mysql functions is there a way to write a query that will return a list of days between two dates.  eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:   2009-01-01   2009-01-02   2009-01-03  2009-01-04   2009-01-05  2009-01-06  2009-01-07  2009-01-08   2009-01-09  2009-01-10  2009-01-11  2009-01-12  2009-01-13   Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).
55577
I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough thing to search.  I currently have a COUNTIF formula that looks like this:  COUNTIF( 'Survey Data'!F2:F200; "Divisional Exec" )   This works fine. But I would like to refine that. So I want to count the number of times a string appears when COLUMN A = 'ACT' and when COLUMN F = 'Divisional Exec' in the same row.  Is this possible to do in Google Spreadsheets? If so how do I do it?  I have tried  but can't seem to get it to work for me.
77188
I have a spreadsheet of my bank account transaction history in this format:    | A          | B        | C        | D 1 | Date       | Amount   | Balance  | Category 2 | 2013-03-10 | $ -10.59 | $3700.76 | Food shopping 3 | 2013-03-11 | $ - 5.10 | $3695.66 | Car parking 4 | 2013-03-11 | $ 100.00 | $4695.66 | Salary   In my spreadsheet the Balance column is hidden because I recalculate it myself.  I want to have a summary for each month with data looking like this:    | A       | B        | C           | D 1 | Month   | Income   | Expenditure | Net 2 | 2013-03 | $ 100.00 |     $ 15.69 | $ 84.31 3 | 2013-04 | $  00.00 |     $ 00.00 | $ 00.00   These are the formulas I have right now, so I can compute Net fine, but I don't know how to compute Income or Expenditure on a monthly basis because SUMIF only lets me do criteria on a single column (in this case, the Date column, but I want to only sum rows &gt;0.0 for Income and &lt;0.0 for Expenditure):  Net = =SUMIF( MonthAndYear( Txn!$A:$A ) , $A$2, Txn!$B:$B )
14041
I want to use LookupOrderedRows and return rows between a start and end date (last month range). What is the best way to handle using Ampscript?   I can add one date to the where using   "DateField", @varStartDate,   But if I add the below to add additional where filters  "DateField", @varStartDate,"DateField", @varEndDate,   I get this error:     "Duplicate Key Field Name: DateField"   EDIT: Also I tried the range as the var for the LookupOrderedRows using   set @strRange = StringToDate(Format(DateDiff(@strStartDate, @strEndDate, "M"),"MM/dd/yyyy"))  and then in the LookupOrderedRows just using  "DateField", @strRange  But that got an error as well.
119305
I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's . People can add tasks to column B and I will assign them to the right people in the team, filling in the other columns. The first field I'm capturing is the date that a new task is logged. I'd like to automate this so that when people add a new task starting in column B, column A gets updated with the date that the new line is created.   I've found scripts that will update the timestamp in column A based on changed in column B, but I only want to log the creation date. In other words, when there is already an entry in column B and it gets updated, the date in column A should not change. Only when column B changes from an empty cell to a new value should column A on that row be updated with the date.  In the above example, if I change the task on line 2, the date in column A should not change. If I add a new task in cell B3, it should add the current date in cell A3.
123200
I am trying to run all three of these in any order but only the third runs:  1.  function onEdit(){   var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   var editedCell = sh.getActiveRange().getColumnIndex();    if(editedCell == 10) {      var range = sh.getRange("A:O");     range.sort([{column: 1}, {column: 14, ascending: true}, {column: 5, ascending: false}]);   } }   2.  function onEdit() {   var sheetName = "PRE-APPRAISAL";   var s = SpreadsheetApp.getActiveSheet();   if (s.getName() !== sheetName) return;   var r = s.getActiveCell();   if( r.getColumn() != 'J' ) { //checks the column     var row = r.getRow();     var time = new Date();     time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm");     SpreadsheetApp.getActiveSheet().getRange('H' + row.toString()).setValue(time);}   }   3.  function onEdit() {   var sheetName = "POST-APPRAISAL";   var s = SpreadsheetApp.getActiveSheet();   if (s.getName() !== sheetName) return;   var r = s.getActiveCell();   if( r.getColumn() != 'J' ) { //checks the column     var row = r.getRow();     var time = new Date();     time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy, hh:mm");     SpreadsheetApp.getActiveSheet().getRange('H' + row.toString()).setValue(time);}   }
85314
I have a database which contains 5 tables. Each table contains 24 rows and each row contains 4 columns.  I want to display these records in Excel sheet. The heading of each table is the name of the table, but I am unable to merge the columns for heading.  Please help me.
43621
I have a movie list with over 1500 entries (A1-A1500), all alphabetized (Thanks to Excel) and I would like to split that into multiple columns of 55 rows each on a separate sheet if possible.  So A1-A55 from Sheet1 would go to A1-A55 on Sheet2, and A56-A110 would go to B1-B55 on Sheet2, so on and so on.  Each cell is an individual movie name and I would just like them to go from one long column to multiple columns with 55 rows in each.
22444
Need help in adding another function using the below script. I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on the below script, I could only have one time stamp, from one column which is the column 2 and time stamp on column 10.  function onEdit()  {  var s = SpreadsheetApp.getActiveSheet();  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet    var x = s.getActiveCell();    if( x.getColumn() == 2 ) { //checks the column      var nextCell = x.offset(0, +8);      if( nextCell.getValue() === '' ) //is empty?        var time = new Date();      time = Utilities.formatDate(time, "GMT", "hh:mm");        nextCell.setValue(time);    };  }; }
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
60442
Is there a way to have a List of tables and figures as one list? Currently I have  \listoffigures  \listoftables   which generates two lists and starts a new page for each list. What I want is a list that contains Tables and Figures in the same list, numbered in sequence (i.e. the first table should get the number following the last figure at that point), Babel-compatible and KOMA-Script-compatible if possible (I use both). The example command  \listoffiguresandtables   should create something like     List of Figures and Tables      1.1 A nice picture caption      1.2 Another nice picture caption      2.1 A caption of a cool table    How to do this?
31399
This is my code:  import datetime today = datetime.date.today() print(today)   This prints: 2008-11-22 which is exactly what I want.  But, I have a list I'm appending this to and then suddenly everything goes "wonky". Here is the code:  import datetime mylist = [] today = datetime.date.today() mylist.append(today) print(mylist)   This prints the following:   [datetime.date(2008, 11, 22)]   How can I get just a simple date like 2008-11-22?
2888
I'm building a quick csv from a mysql table with a query like:  select DATE(date),count(date) from table group by DATE(date) order by date asc;   and just dumping them to a file in perl over a:  while(my($date,$sum) = $sth-&gt;fetchrow) {     print CSV "$date,$sum\n" }   There are date gaps in the data, though:  | 2008-08-05 |           4 |  | 2008-08-07 |          23 |    I would like to pad the data to fill in the missing days with zero-count entries to end up with:  | 2008-08-05 |           4 |  | 2008-08-06 |           0 |  | 2008-08-07 |          23 |    I slapped together a really awkward (and almost certainly buggy) workaround with an array of days-per-month and some math, but there has to be something more straightforward either on the mysql or perl side.   Any genius ideas/slaps in the face for why me am being so dumb?    I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons:   I know the date range I'll be looking for every time The server in question unfortunately was not one that I can install perl modules on atm, and the state of it was decrepit enough that it didn't have anything remotely Date::-y installed   The perl Date/DateTime-iterating answers were also very good, I wish I could select multiple answers!
9755
for a few days I'm stuck at comparing two dates.  In a big table with dates as columns and products as rows, we want to display the amount of sales per day per product.  What the following query does:   Import our sales via IMPORTRANGE query the specific sales for a given day and product ID return 0 if there is an error (e.g. not found) count the result   This is a working query with the date 2018-03-26 hard coded:  =COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '2018-03-26'"; 0)))  It shows how many sales a specific product has on the specified date.  Now I want the date from a cell in the sheet:  =COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '"&amp;$AR3&amp;"'"; 0)))  But this doesn't work. I tried A LOT of things for the past 3 days but I have no idea if I did that wrong (I'm a beginner) so I'm turning to you. What might be wrong with the date comparison in this section?  "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '"&amp;$AR3&amp;"'"  You can see it  in Column AR Row 95 but I can't give edit rights.