Monday, September 5, 2011

yogi_Flip Contents Of Two Columns With A Single Formula And Post Results In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
rajvivek said:
How to Flip two columns contents with a SINGLE formula?
How is it possible to have a SINGLE formula to pull the content from column A to column D and from B to column C?
--------------------------------------------------------------------------
There is no direct way to do it ... however
A.  1) I can do it in another sheet, say Sheet2
      2) if column1 row1 of data is blank (or inconsequential)
      3) if there is a blank column available to the left of where data would be posted in Sheet2
or
B.  It can be done using the QUERY function, as shown in Sheet3

so here we go ...

2 comments:

  1. Can't you just use =query() and just do something like: = query(sheet1A:B, "select B,A") in C1?

    ReplyDelete
  2. Hi Ted:

    Since I have assumed mixed data type in columns A and B of original data, use of QUERY function is not straight forward.

    However ... I have added another sheet using the QUERY function -- but since I have assumed the more generalized case of mixed data type in the columns of the original data, I converted the data to be queried first into all TEXT, then in the end result the numerics show up as TEXT -- this limitation may or may not be acceptable to the OP.

    Cheers!
    Yogi

    ReplyDelete