How to move all data to one column

Question

how do I make a data that look like below picture and change it to "to be"

The picture will explain. i have a very long row of data that need to move in to 1 row, is there a easy way todo without copying and transpose row by row

Answer 1

You can use Power Query to get the result:

1. Select Range and go to Data- From Table/Range (uncheck "My table has a header"):

2. Select all Columns- Go to Transform- Unpivot Columns- Remove column Atribute:

3. Close and load data:

Answer 2

Certainly! If you have a very long row of data that you want to consolidate into one row without copying and transposing row by row, you can use the "TRANSPOSE" function along with some array manipulation. Here's a step-by-step guide:

Suppose your long row of data is in cells A1 to ZZ1, and you want to transpose it into a single row starting from cell A2.

Transpose the Data:

In cell A2, enter the following formula:

=TRANSPOSE($A$1:$ZZ$1)

Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces {} appear around the formula in the formula bar. Adjust Formatting (Optional):

If needed, you might want to adjust the formatting, such as column width, to ensure that the transposed data fits well. This formula transposes the data from a row to a column. The $A$1:$ZZ$1 references your original row, and the TRANSPOSE function flips it into a single column. The curly braces indicate that it's an array formula.

Now, you have your data in a single column in column A, and you can move it or copy it to another location if needed.