Hello,
my name is Michael and I hope somebody can help me.
I attached an image to describes my issue.
I have a large sheet with ~80000 rows and ~5000 columns and I would like to concatenate some columns.
I am able to do what I want manually, but I need a more efficient way.
I don't know how to teach excel to continue following sequences:
Row-1: Every third cell: B1,E1,H1,...
Row-2: Concatenate B2,C2,D2|E2,F2,G2|H2,I2,J2|K2,..
Best regards from Vienna
Michael
concatenate-columns.PNG
If you post a sample workbook with the results you want it will be much easier to help you.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Hi,
probably due to my bad explanation it took a while to get an answer on the forum.
In the meantime I found a solution, that could maybe be improved, because the soultion slows down excel, but at least it works.
I thought I post my solution so that this thread might be useful for other users.
=INDIREKT("'Import_get_them_all-sdf'!"&ADRESSE(ZEILE();SPALTE()*3-2))
=VERKETTEN(INDIREKT("'Import_get_them_all-sdf'!"&ADRESSE(ZEILE();SPALTE()*3-2));" ";INDIREKT("'Import_get_them_all-sdf'!"&ADRESSE(ZEILE();SPALTE()*3-1));" ";INDIREKT("'Import_get_them_all-sdf'!"&ADRESSE(ZEILE();SPALTE()*3-0)))
Sorry for the german formulas, but if you import the attached file, you will have it in your language.how-to-concatenate-every-third-cell.xlsx
Best regards
Michael
Here is my attempt in A2, just copy in any direction. The INDEX range need to be extended of course but otherwise it works. However, it is not any better or shorter or faster than your formula. Pretty much anything you do to 8000 x 5000 cells will slow down your computer.
Formula:
Please Login or Register to view this content.
Thank you, with INDEX it works too. So my topic is solved.
I am not sure about this, but what I ment with slow down is that with INDEX and INDIRECT xcel calculate the whole sheet even if you just change one cell.
I think this is called volatility. So I just stopped xcel to update formulas automatically (can be changed in options) and update them manually with F9.
I just guess with a VBA solution xcel wouldn't need about seconds to calculate if I just change one or few cells.
However, at the moment I am happy with my solution.
You are right. INDIRECT is volatile and INDEX is NOT volatile according to this site:http://www.decisionmodels.com/calcsecretsi.htm
Maybe using INDEX formula will be enough?
Setting the calculation to manual is also a good option.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks