Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to transform dataset from rows to columns using combination of advanced functions. Let’s get started.
SAMPLE DATASET
The screenshot below shows the sample dataset in two columns.
DATA TRANSFORMATION
We want to write a single formula that will split all the values in the Total Sales column to each rows based on the Account Manager in column A. To perform this operation, we will use combination of many functions such as LET, TEXTSPLIT, CONCAT, HSTACK, IF and TOCOL function.
- To right the formula: in cell D2, execute the formula: =LET(v,–TEXTSPLIT(CONCAT(B2:B7&”-“),”,”,”-“,1),HSTACK(TOCOL(IF(v,A2:A7),2),TOCOL(v,2)))
- Click OK. The formula will spill to column E and the rows below.
From the screenshot below, you can see that we have successfully transform the data nicely!
See you in the next tutorial