Suppose you have data that is stored in long format in excel. You want to reshape it to wide format.
The data is shown in the image below : -
Step I : To pull unique values from Column A
Type the following formula in cell D2
Step II : Reshape data from long to wide format
Type the following formula in cell E2
=IF(COLUMN(A1) <=COUNTIF($A$2:$A$100,"="&$D2),OFFSET(INDIRECT("$B"& COUNTIF($A$2:$A$100,"<="&$D1)+2),COLUMN(A1)-1,0),"")
Paste the formula to the right and down so that transformation happens correctly. You can use CTRL + R shortcut key to paste the formula to the right and CTRL + D shortcut key to paste the formula down.
Download the workbook
The data is shown in the image below : -
 |
| Reshape data from long to wide format |
Step I : To pull unique values from Column A
Type the following formula in cell D2
=IFERROR(INDEX($A$2:$A$100, MATCH(0,COUNTIF($D1:D$1, $A$2:$A$100), 0)),"")Press CTRL + SHIFT + ENTER to confirm this formula as it's an array formula. If this formula is entered correctly, you would see the formula inside the curly brackets {}.
Step II : Reshape data from long to wide format
Type the following formula in cell E2
=IF(COLUMN(A1) <=COUNTIF($A$2:$A$100,"="&$D2),OFFSET(INDIRECT("$B"& COUNTIF($A$2:$A$100,"<="&$D1)+2),COLUMN(A1)-1,0),"")
Paste the formula to the right and down so that transformation happens correctly. You can use CTRL + R shortcut key to paste the formula to the right and CTRL + D shortcut key to paste the formula down.
Download the workbook
Excel Tutorials : 100 Excel Tutorials
About Author:
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.
While I love having friends who agree, I only learn from those who don't.
The data in Column A must be numbers?
ReplyDeleteI tried text data and got wrong results
yes, it must definitely be numbers, in order
DeleteThe second COUNTIF should be a MATCH() instead. It should be:
ReplyDelete=IF(COLUMN(A1) <=COUNTIF($A$1:$A$100,"="&$D2),OFFSET(INDIRECT("$B"& MATCH($D2,$A$1:$A$100,0)),COLUMN(A1)-1,0),"")