Friday, February 17, 2012

yogi_Align Columns Of Data Using One Comment Field


Yogi Anand, D.Eng, P.E.                                      Google Spreadsheet                       www.energyefficientbuild.com


user Wes.vasher said:
Align columns of data usine one comment field?
I have a master list of all 1000 customers with a login column, email column and other columns such as address, phone etc.
I have another subset of this list with around 500 customers with a login column and one other column of data.
I have them both in the same spreadsheet and want to combine the list using the common login column, that is, align the correct rows of the 500 list to the 1000 list.
I've tried everything I can find without success and am turning here for help. Any help would be greatly appreciated!
Align columns of data "using" one "common" field that is. I've added a link to a sample spreadsheet below.
https://docs.google.com/spreadsheet/ccc?key=0Aigg8OpP5H89dGc4Q3BNanFIMUppaGpWVWxPaGlUNnc
------------------------------------------
following is a solution to the problem:

Posted by at
Labels: , , , , ,

4 comments:

  1. Carlos HGFebruary 24, 2012 at 11:42 AM

    I run the formula without the two index functions and it also works fine.

    Cheers !

    ReplyDelete
  2. Yogi AnandFebruary 24, 2012 at 8:16 PM

    Hi Carlos HG:

    Thanks for your comment ... however, I just checked again, and from how I look at it I need both of the INDEX functions. Would you please elaborate and perhaps share reference to a spreadsheet or an image where my formula using only one index function does the job.

    Cheers!
    Yogi

    ReplyDelete
  3. Carlos HGFebruary 28, 2012 at 12:43 PM

    Hi Yogi,

    In my first comment I tried to explain that the formula doesn't need any index function.
    I use the next formula in a copy of your spreadsheet and is working:

    =arrayformula(if({1,1,1,1,1,0,0},A2:E,if({0,0,0,0,0,1,0},iferror(vlookup(D2:D,F2:G,1*row(D2:D)^0,0)),iferror(vlookup(D2:D,F2:G,2*row(D2:D)^0,0)))))

    Cheers !

    ReplyDelete
  4. Yogi AnandFebruary 28, 2012 at 1:44 PM

    Hi Carlos HG:

    I know what you mean now ... I had used the same VLOOKUP function twice and used the INDEX function with the same VLOOKUP function to pull the values from the first and second column, and you used different VLOOKUP functions ... once to pull values from one column and then a different VLOOKUP function to pull the value from the second column.

    I had thought you meant you were able to do what I did using VLOOKUP function only once.

    Cheers!
    Yogi

    ReplyDelete

Subscribe to: Post Comments (Atom)