Wednesday, March 6, 2013

yogi_Pull In A Separate Sheet Only Selected Columns of Data from Another Sheet Meeting A Specified Criterion


                                          Google Spreadsheet   Post  #1068
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 6, 2013
user Subs22 :(http://productforums.google.com/forum/?zx=930pcblhic5b#!mydiscussions/docs/IlsPEbac39A)
How do I transpose every three cells of a function output?
I am using the following formula to import data from one sheet to another based on certain criteria.  I want to be able to transpose every three cells of that data.

=ARRAYFORMULA(SPLIT(SUBSTITUTE(CONCATENATE(SUBSTITUTE(TRIM( SUBSTITUTE(FILTER(Data!A:A,Data!C:C=G1);" ";CHAR(9)) & " " & SUBSTITUTE(FILTER(Data!B:B,Data!C:C=G1);" ";CHAR(9)) & " " & SUBSTITUTE(FILTER(Data!C:C,Data!C:C=G1);" ";CHAR(9)) & " ") ;" " ;CHAR(10))&CHAR(10));CHAR(9);" "); CHAR(10)))

For instance, if the data is:
A |  B |  C |  D | E | F
1  |  2 |  3  |  4 | 5  | 6 
I want it to be displayed on the new sheet as:
A | B | C
1  | 2 | 3
4  | 5 | 6
---
Yogi,

No problem, I will try again.

Your formula works if I only want to pull information from columns A, B, and C.  But suppose I want to skip column C and pull information from columns A, B, and D.

----------------------------------------------------------------------------------------------------------------------

your formula is doing real haeavy duty lifting while simpler solutions are available ... let us have Google spreadsheet do the heavy lifting