Here, we will learn how to combine multiple lists into a single list using the Excel WRAPROWS and TOCOL functions in one formula.
The dataset (shown in the image) contains three lists; each list containing employees and their monthly salaries. The goal is to convert all the three lists into one single list. To accomplish the task, we create the header columns "Name" and "Salary" in the cells A9 and B9 respectively and use the WRAPROWS and TOCOL functions in one formula.
The formula in A10, is:
=WRAPROWS(TOCOL(A4:F7),2)
As soon as I press Enter, the formula spills the results into the multiple cells automatically in the range A10:B21.
How this formula works
TOCOL transforms the array A4:F7 into a single column, scanning the values by row and returns the array to WRAPROWS. WRAPROWS wraps the array into rows that each contain 2 values. Therefore, we get the three lists into a single list.
Note that the same result can be achieved by using the VSTACK function also. The formula in D10, is:
=VSTACK(A4:B7,C4:D7,E4:F7)
Here, VSTACK appends each list to the bottom of the previous list.
You have successfully learnt how to combine multiple lists into a single list using the Excel WRAPROWS and TOCOL functions in one formula! I hope this post helped you.
