I am having 12 columns which are getting auto filled up by the formula "IMPORTRANGE".
I need to collate all the table values to a single value (condition is it should behave like as "ARRAYFORMULA").
Just wondering is there any specific formula/query exist for this purpose.
I have added the sample snapshot of my sheet. I need these RED highlighted values in a different column, let's say at "EF".
I'll show sample formula for 3 columns, but you may use the same logic for any number of columns.
the formula is
=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(FILTER(A2:C,A2:A>0)&"-"))),"-"))
A2:C is the range with all your columnsA2:A>0 is the condition by column A to choose only proper rows. This may by A2:A<>"" for text column or any condition to limit number of rows.- in the formula is any symbol which is not the part of original data. You may use group of symbols, or even special words for the same purpose: =TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(FILTER(A2:C,A2:A>0)&"devide"))), "devide"))not continuous columns
If you have data are not in continuous columns.
If so, then you need to change this part of formula FILTER(A2:C,A2:A>0) into query:
=query(A:AO;"select A,I,Q,Y,AG,AO... where A > 0")
or better this:
=query({A:AO};"select Col1, Col6, Col20, Col22, ... where Col1 > 0")
And the final formula will look like this:
=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(query({A:AO};"select Col1, Col6, Col20, Col22, ... where Col1 > 0")&"-"))),"-"))
not continuous columns, data is numbers and text
When you have both numbers and text as input into your data, query won't work because it can handle only one type of data in one column.
Your simplified case will look like this:
The first task is to grab all columns with the name "Ticket number" into one table.
Let's work in the sheet "Report", use filter function:
=FILTER(Data!1:1001,Data!2:2="Ticket number")
and get the result:
Step 2 is implementing the first formula:
=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(Report!A3:D&"-"))),"-"))
The other way is to combine columns one by one:
={A2:A5;B2:B5;C2:C5}
If you like this method, you may read more here.
Max's answer is very good and I'd recommend using that if it solves your problem.
In case you want a bit more flexibility though, I have written a library called Reshape that allows you to melt data in a long format like you desire.
You could run something like:
=MELT(IMPORTRANGE(<spreadsheet>, "A:DY"), , , , 1)
It puts column headers tinto their own column to make identification clear which may or may not be what you want (since you only talk about putting values into one columns which is what this script does). Take a look at the two tables in the link to see what the function conceptually does and see if it's right for you.
I don't have it published as an add-on outside of my network so you'd need to copy and paste the code into your script
