logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Excel: Merge Multiple Columns into One, Rearrange Rows Left to Right - Formula Solution

chyter 19538 7
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
| New topic
  • #1 10797432
    chyter
    Level 11  
    Hello,
    How to regroup data from several columns to one, i.e. if I have data, for example
    2 4 3 7
    3 5 8 3
    4 6 7 7
    to one column in such an order that first the first row from left to right, then the second row, etc., so that there is one column of data, i.e.:
    2
    4
    3
    7
    3
    5
    8
    3
    4
    6
    7
    7
    I will add that I have a lot of these poems, so it would be best if there was a formula for it.
  • ADVERTISEMENT
  • #2 10798237
    walek33
    Level 29  
    I have no idea about the formula. :cry:
    The surest way remains - VBA.
  • ADVERTISEMENT
  • Helpful post
    #3 10798295
    mad_pablo
    Level 13  
    The fastest way to do it without excel ;)
    1. Install Notepad++ (Freeware)
    2. Copy the contents of the sheet to it. Or save the sheet as csv and open in Notepad++
    3. In Notepad++, replace the tab character "\t" (if you were copying) or the semicolon with a space "; " (if you were saving csv) with a newline ("\n"). You need to use extended search
    4. Copy the result to excel.
    Delivery time 30 seconds + N++ installation.

    It could be done with a formula, using the ROW, VALUE and WK search functions, but why?
  • ADVERTISEMENT
  • Helpful post
    #4 10798402
    marek003
    Level 40  
    For me personally, VBA is a last resort.
    Outputting something outside of excel as it is already in excel and re-reading it later in excel is also not entirely accurate, but ... you can.
    As they say every road leads to Rome

    I propose, for example, like this (after "excel"):
    =INDIRECT(ADDRESS(ROW()/4,1),ROW()-ROUNDDOWN(ROW()/4-0.01,1)*4))

    or
    =INDIRECT(ADDRESS(ROUND(ROW()/4,1),ROW()-ROUND(ROW()/4,1)*4+4))
    Attachments:
  • ADVERTISEMENT
  • #5 10798434
    mad_pablo
    Level 13  
    If it can be assumed that each row has the same number of columns, then of course. @chyter: Of course you need to convert 4 to the number of columns in the row.
  • #6 10798465
    marek003
    Level 40  
    mad_pablo wrote:
    ... assuming that each row has the same number of columns ...


    Of course. I didn't mention it (And to be honest, it didn't even occur to me that it could be otherwise)
  • #7 10798478
    mad_pablo
    Level 13  
    marek003 wrote:
    mad_pablo wrote:
    ... assuming that each row has the same number of columns ...


    Of course. I didn't mention it (And to be honest, it didn't even occur to me that it could be otherwise)


    I've cut myself a few times, so I've developed a procedure that's immune to this condition.
  • #8 10804850
    chyter
    Level 11  
    Thanks for your help, everything works fine :)
| New topic
Report a violation of the law

Topic summary

The discussion revolves around merging multiple columns of data into a single column in Excel, specifically rearranging rows from left to right. Users suggest various methods, including using VBA as a last resort, and alternative solutions like utilizing Notepad++ to manipulate data before importing it back into Excel. A formula solution is proposed using the INDIRECT and ADDRESS functions, with the caveat that the number of columns must be adjusted accordingly. The conversation emphasizes the importance of finding efficient methods to handle large datasets within Excel.
Summary generated by the language model.
ADVERTISEMENT