So I have this spreadsheet with data in it, there are 29 columns and 54 rows.  On the 2nd sheet I'm trying to find all of the rows that fit a certain criteria.  For some reason, if I include the column X in my query data, the results are completely messed up. The 1st row of the result is just concatenating the first 23 rows together whether they fit the criteria or not. If I only include up to Column W the query is OK and it returns the correct results. But the problem is that I need to get data from Columns A and AB, so I need to include column X in my data range.  In this spreadsheet you can see the data on Sheet1, the query that includes column X on Sheet2, and on Sheet3 I have the same exact query except it only goes up to Column W and you can see the correct results there.  Basically, I need the query to return the value of Column A and Column AB for every row where Column B is marked with an "x".
124140
I'm getting some really weird results from my QUERY() Here is an image with an example:    I'm querying for the name where No Show = "No Show". It find the correct name Name 5 but also returns a concatenated list of names separated by a space.   Why is this happening? How do I fix it?
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?
35680
I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In my actual application the range is found by an array formula so I'm not sure if non array formulas will work here. My original function was {=LARGE(IF($A:$A=$A2,$B:$B),2)} dragged down the column. Replace 2 with 3 for the third largest, 4 for the fourth largest, etc. This worked except in the case of duplicates.  For example:  The data is in column 1 and the desired output is in column 2:  1    5  2    4 2    3 3    2  3    1 3 4 4 5   Things I have tried:   {=LARGE(IF(A:A &lt; LARGE(A:A,i-1),A:A),1)} for the i-th largest value.    -Inputting this gives 3 for both i = 3 and i = 4. =LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1)   -This doesn't seem to be any different than just LARGE(A:A,2) {=MAX(IF(A:A &lt; LOOKUP(9.99999999999999E+307,A:A),A:A))}  -This does retrieve the value 4 but I'm not sure how to generalize it to the kth largest value.    VBA solutions are fine as well. I tried to code one myself but wasn't sure how to code in the array formula IF($A:$A=$A2,$B:$B).
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
98116
I have a calculated column CRM ID with simple formula =CONCATENATE("CRM")&amp;ID. The formula concatenates ID field with string CRM.  The issue is: Sometimes when user update list records, the CRM ID field doesn't show ID.It shows only CRM string. This issue is intermittent.   Please advice.
66459
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.  I need a script that can arrange the data like in the blue rectangle.
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)
68798
I have data in an Excel file that I want to join with a shapefile in my project. The Excel file contains values like volumes. The Excel file and the shapefile I want to join it with both contain the same property names.  The problem is, when I join the two, I get too many decimals in the attribute table. I get six, but I only want it to be one.  I tested to change the format of the cells to one decimal, and then I saved the file as *csv. But, somehow I seem to loose data in the process? It should be 42 rows that contains data in the column i am working with, but I end up with only like 10?  Another thing is that my property names contains the letters Å. Ä and Ö, perhaps this complicates things?  I read the question , but it doesn't mention the problem with the data loss.  My question was tagged with , but I didn't write what version I'm using. I use QGIS 2.2.0. Would it help to upgrade to the latest version?
59018
I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name. The cells in the sheet contain calculated agency populations for each date.   This is a reference sheet: "DailyPopulation"  In the ReportSheet the user defines a Period Start Date and Period End Date  I'm trying to construct an array formula that displays the max population for each agency within the defined period, separately (of course) I also want the min.
24577
For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15.  How would I go about doing this?
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
92183
I've got about a billion rows of data in a table with a name and an integer in the range 1-288.  For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.  This query generates an example case:  --what I have: SELECT * FROM ( VALUES ('foo', 2),               ('foo', 3),               ('foo', 4),               ('foo', 10),               ('foo', 11),               ('foo', 13),               ('bar', 1),               ('bar', 2),               ('bar', 3)      ) AS baz ("name", "int")   I'd like to generate a lookup table with a row for each name and sequence of contiguous integers.  Each such row would contain:  name   --  the value of the name column start  --  the first integer in the contiguous sequence end    --  the final value in the contiguous sequence span   --  end - start + 1  This query generates example output for the above example:  --what I need: SELECT *  FROM ( VALUES ('foo', 2, 4, 3),               ('foo', 10, 11, 2),               ('foo', 13, 13, 1),               ('bar', 1, 3, 3)      ) AS contiguous_ranges ("name", "start", "end", span)   Because I have so many rows, more efficient is better.  That said, I only have to run this query once, so it isn't an absolute requirement.  Thanks in advance!  Edit:  I should add that PL/pgSQL solutions are welcome (please explain any Fancy Tricks--I'm still new to PL/pgSQL).
11546
Been looking to pivot rows (progress_check) into columns check 1, check 2 etc... No sum or totals required, just to display the results hopefully... Can anyone help, Thanks Ad
54570
In my excel sheet I have three columns A, B, C that are very thin, but have a lot of lines (>500). Now if I print this out, I get 10 pages that are almost empty. Only at the left border of each page I see this three columns.  What I want to do, is to print that whole sheet in a multicolumn layout. 2 or 3 columns per page would be enough. Currently, I'm doing it by copying the values out of Excel, inserting it into a Word document and then use the multi-column layout function of Word. Isn't there a way to achieve that directly in Excel?  To clarify my problem: Currently my pages look like that  A B C A B C A B C A B C . . .   But what I want is this:  A B C     A B C     A B C A B C     A B C     A B C A B C     A B C     A B C . . .     . . .     . . .
48546
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this  trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.  The data looks like this:  id  row_num  customer_code comments ----------------------------------- 1   1        Dilbert        Hard 1   2        Dilbert        Worker 2   1        Wally          Lazy   My results need to look like this:  id  customer_code comments ------------------------------ 1   Dilbert        Hard Worker 2   Wally          Lazy   So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.   My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.
110578
I have a spreadsheet in Google Docs with two data rows and lots of columns like so:    Is it possible to easily convert these rows into two columns and lots of rows (obviously retaining all information and formulas)? In other words, to something like this:   19.9.2009   967,15 €  28.9.2009   950,78 € 12.11.2009   942,03 € ..           ..   There's probably a neat mathematical term for such a conversion, but I don't remember which (matrix something?). Feel free to suggest a better name for the question.  Edit: To clarify, ultimately I want to get rid of the original (two row) version and continue working on the converted one. TRANSPOSE(), , got me very close but not quite there. I want the raw data in the transposed version, not cells that mirror the original ones.
73208
How can I get Excel to increment ranges arguments by more than one row when I fill down a formula?  For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the average function to keep it in a pattern throughout, without changing it from an increment of one from the first and last number of the previous formula?  Excel keeps giving me this when I fill down:  =Average(A1:A5) =Average(A2:A6) =Average(A3:A7)   I want Excel to do this instead:  =Average(A1:A5) =Average(A6:A10) =Average(A11:A15)   How can I do this by filling down?