In this topic I want to share a beautiful trick to convert a table data in to a single column. I have faced such problem while arranging the data in desire format.
I have taken example of 4 rows and 7 columns table with numerical values for better understanding. From the example you can easily understand that the desired result arranged by placing the values by going across column till the last values then go to starting value of next row.
There are many ways to achieving this. You can do it by copy-paste the value of one row at a time but it will take lot of time for larger table.We'll get the result by using 2 different method.
Method 1 : By Using of Index Function
In this method it requires some preparations. But on the other hand, it’s one of the faster way. Let’s see what you need to prepare. Basically you have to create the column and row number in additional helper columns.
As per above table (4 rows X 7 columns).
Row helper Column values are each row number repeated by (number of column) times.
Column helper column values are repeated the data set (starting with 1 to number of columns) by (number of row) times.
This is how created a helper column for the above example. In this way, you will also create a helper column according to your table.
Now using the Index function
=INDEX($A$2:$G$5,I2,J2)
The formula pulled the data by using the values of both helper columns. There are some "0" in the results which have come due to the empty space of the table. You can delete the 0 by filtering.
You have seen how you can transform table data into a single column by row wise. Similarly you can transform by column wise.
For this, you have to do a little change in the helper columns. Below the screenshot of example of transform the data table to single column by column wise.
Method 1 : By Using of Pivot table
Here we will learn how to transform table data into single column by using pivot table.This is also a very easy method and it is beautiful trick for large tables.
For this also you have to do some basic preparation. As we know that the table heading for the pivot table should be maintained. Here is the screenshot of previous data table with heading.
In this trick, you have to follow some steps.
First you need to select the table by dragging technique or using shortcut key (Ctl+A).
Go to Insert menu and select PivotTable. and click on OK.
Here the screenshot of pivot Table will appear in another worksheet.
Now select all column (from C1 to C6) one by one and place in sorting manner Row Labels.
Right now your pivot table data is in the form of our desired output, but it does not look like a proper output. Copy the pivot table data now and paste as "value" in another cell.
Now filter the cell written blank and delete them. Take your table data transferred to single column.
In this trick, you have transferred your table data (row wise) into a single column.
But transfer your data (column wise) , you have to transpose your table first and then follow all the same steps.
