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.
Value A2_11 is missing and value A2_01 has an extra line.
ReplyDeleteI 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))))
Hi Carlos HG:
ReplyDeleteThanks for your comment -- I now have updated the formula as you suggested.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogia,
ReplyDeleteThanks 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!
Hi Admin:
ReplyDeleteSo Done!
Cheers!
Yogi
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!
ReplyDeleteHi Admin:
ReplyDeleteI 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
Sorry, that was bad explaining. Here's a page I made: https://docs.google.com/spreadsheet/ccc?key=0AmrjXVhLBq5NdHNEU095RkZlSnl2UlBMZjFYSXByU2c#gid=0
ReplyDeleteIn columns D and F, the last number/letter gets cut off. It probably has something to do with the header being there.
Hi Admin:
ReplyDeleteYou 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
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.
ReplyDeleteYour help is much appreciated!
Hi Admin:
ReplyDeleteI 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
This comment has been removed by the author.
ReplyDeleteActually, 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?
ReplyDeleteHi Admin:
ReplyDelete"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
I really wish I knew how to do that. I would definitely owe you one if you were able to do that for me.
DeleteHi Admin:
ReplyDeletePlease 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
This comment has been removed by the author.
ReplyDeleteThis is perfect! Thank you. Your efforts will not go un-rewarded.
ReplyDeleteQuestion: 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!
Hi Admin:
ReplyDeleteSorry, 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
Here's an example: https://docs.google.com/spreadsheet/ccc?key=0AmrjXVhLBq5NdHNEU095RkZlSnl2UlBMZjFYSXByU2c&pli=1#gid=0
ReplyDeleteIn 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?
Hi Admin:
ReplyDeleteYour 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
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.
ReplyDeleteP.S. I have been helping you on the advertising side of things, if you know what I mean.
Hi Admin:
ReplyDeleteI 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
Works perfect! thanks~!
ReplyDeleteYou Are Very Welcome Admin ... Now Let Us Keep Googling.
ReplyDeleteCheers!
Yogi