Wednesday, February 22, 2012

yogi_Merge Data In A Column From Different Sheets By Appending Consecutively

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

user SimonNY said:
How to consecutively add INDEX
How can I pull column data from multiple columns? The current formula I have merges the data together, but I want it to be consecutively added instead.
=INDEX(INDEX(Sheet1!A:A)&INDEX(Sheet2!A:A))
Thanks!
-------------------------------------------------
following is a solution to the problem where I have appended the data from column A of Sheet1, and then to that appended data from column A of Sheet2.

24 comments:

  1. Value A2_11 is missing and value A2_01 has an extra line.

    I suggest the next modification:

    =arrayformula(transpose(split(concatenate(indirect("Sheet1!A1:A"&counta(Sheet1!A:A))&char(9),indirect("Sheet2!A2:A"&counta(Sheet2!A:A))&char(9)),char(9))))

    ReplyDelete
  2. Hi Carlos HG:

    Thanks for your comment -- I now have updated the formula as you suggested.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  3. Hi Yogia,

    Thanks for the solution! I'm actually trying to combine 4 columns instead of the two you listed. I've been trying to manipulate 2 more columns in there, but my lack of programming intelligence isn't allowing me to. Would you be so kind as to add two more columns in there? Thanks!

    ReplyDelete
  4. Awesome! Why does it that when I have multiple columns using this formula, each proceeding column has less/lost data than the last? Is there a way to remedy that? Thanks again!

    ReplyDelete
  5. Hi Admin:

    I don't know what you mean by

    'Why does it that when I have multiple columns using this formula, each proceeding column has less/lost data than the last?each proceeding column has less/lost data than the last?'

    why should any data be lost?

    please explain clearly with an example ... what you mean ...
    and then let us take it from there.

    Cheers!
    Yogi

    ReplyDelete
  6. Sorry, that was bad explaining. Here's a page I made: https://docs.google.com/spreadsheet/ccc?key=0AmrjXVhLBq5NdHNEU095RkZlSnl2UlBMZjFYSXByU2c#gid=0

    In columns D and F, the last number/letter gets cut off. It probably has something to do with the header being there.

    ReplyDelete
  7. Hi Admin:

    You are right about the header being there in column A of different sheets.
    To get correct results for concatenating data in column B of different sheets ...

    1) put headers in column B of Sheets, then

    2) use the following formula:
    =ArrayFormula(transpose(split(concatenate(indirect("Sheet1!B2:B"&counta(Sheet1!B:B))&char(9), indirect("Sheet2!B2:B"&counta(Sheet2!B:B))&char(9),indirect("Sheet3!B2:B"&counta(Sheet3!B:B))&char(9), indirect("Sheet4!B2:B"&counta(Sheet4!B:B))&char(9)),char(9))))

    Cheers!
    Yogi

    ReplyDelete
  8. hmm...there's already headers there and it doesn't look like that formula worked. I made the sheet editable by anyone so you can tinker with it.

    Your help is much appreciated!

    ReplyDelete
  9. Hi Admin:

    I don't believe I am understanding you -- the formula in cell D1 in Sheet1 is showing correct result
    and
    if you put headers in column B of your Sheet2, Sheet3, and Sheet4, and use the following formula in cell E1 of Sheet1

    =ArrayFormula(transpose(split(concatenate(indirect("Sheet1!B2:B"&counta(Sheet1!B:B))&char(9), indirect("Sheet2!B2:B"&counta(Sheet2!B:B))&char(9),indirect("Sheet3!B2:B"&counta(Sheet3!B:B))&char(9), indirect("Sheet4!B2:B"&counta(Sheet4!B:B))&char(9)),char(9))))

    that will also give correct result in cell E1

    Cheers!
    Yogi

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Actually, you're right - my mistake. The problem I'm having is that some of the cells are purposefully left blank. It looks like the formula would skip the cell if it was empty. Is there any way to still count and include all cells (including blanks) up to the last cell with data?

    ReplyDelete
  12. Hi Admin:

    "Is there any way to still count and include all cells (including blanks) up to the last cell with data?"

    Sure ... instead of counting the cells in a column, compute the row number of the last occupied cell in that column and use that instead of the the count of cells with header in the cell of first row of the column.

    Cheers!
    Yogi

    ReplyDelete
    Replies
    1. I really wish I knew how to do that. I would definitely owe you one if you were able to do that for me.

      Delete
  13. Hi Admin:

    Please have a look at my following blog post ...
    yogi_Merge Data In A Column Including Blanks From Different Sheets By Appending Consecutively
    http://yogi--anand-consulting.blogspot.com/2012/03/yogimerge-data-in-column-including.html

    Please have a look at the post to see how this works for you.

    Cheers!
    Yogi

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. This is perfect! Thank you. Your efforts will not go un-rewarded.

    Question: Is it possible to reference a cell that is already formulated? I'm trying to use the FILTER function to reference the formulated cells.


    Cheers!

    ReplyDelete
  16. Hi Admin:

    Sorry, I don't know what you mean ... please explain with some sample data and your expected result so I can clearly see what you are intending to accomplish.

    Cheers!
    Yogi

    ReplyDelete
  17. Here's an example: https://docs.google.com/spreadsheet/ccc?key=0AmrjXVhLBq5NdHNEU095RkZlSnl2UlBMZjFYSXByU2c&pli=1#gid=0

    In column F, I'm trying to filter to show all Data X that has a corresponding column equal to "a". Should I be using a different formula?

    ReplyDelete
  18. Hi Admin:

    Your formula is OK ... looks like you have some other isues to deal with. I know you have been at it for quite aome time now ... so if you want to reach out to me via https://vCita.com/yogi.anand, set up a phone meeting, then we can look at your spreadsheet interactively and hopefully resolve the problem to your satisfaction.

    Cheers!
    Yogi

    ReplyDelete
  19. Again, I thank you for all you've done so far and I'm sure you're tired of dealing with my problems by now so I won't take up any more of your time.

    P.S. I have been helping you on the advertising side of things, if you know what I mean.

    ReplyDelete
  20. Hi Admin:

    I had a little play with your spreadsheet ... and let me suggest you try ...

    =filter(D:D,search("a",E:E))

    Let me know if this work for you now?

    Cheers!
    Yogi

    ReplyDelete
  21. You Are Very Welcome Admin ... Now Let Us Keep Googling.

    Cheers!
    Yogi

    ReplyDelete