CODEWITHSUNDEEP
excelconcatenation
Caroline J.
Caroline J.

Reputation: 27

Combining an array with multiple columns into a single column using Excel or Python

I am working with a large array of values in Excel. These values are organized vertically (starting at the top for each column) and span across 194 columns. I want to stitch each column together into a single column (so that I can copy/paste all of the values into a separate program for additional calculations). Basically, starting with the first column, I want to take the column on the right and add the values to the column on the left (so that the value at the top of the column being added comes after the value at the bottom of the column preceding it). I want to do this for all columns in the spreadsheet.

I haven't been able to figure out how to do this in Excel or through my Jupyter notebook (after converting the Excel file to a .csv and trying to use a loop to stitch the files together).

Any help would be greatly appreciated!

Upvotes: 0

Views: 972

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If you can use Power Query (available in Excel 2010+ and Office 365), you can do it with just a few lines of code.

let

//change the table name in the next line to the real table name in your worksheet
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    oneCol = Table.FromList(List.Combine(Table.ToColumns(Source)),Splitter.SplitByNothing())
in
    oneCol

If your columns do not all have the same number of rows, you may have to RemoveNulls or do some other processing depending on the nature of your data and desired output

eg:

oneCol=Table.FromList(List.RemoveNulls(List.Combine(Table.ToColumns(Source))),Splitter.SplitByNothing())

enter image description here

Upvotes: 1

Related Questions