Hello everyone
I have a huge excel data that I want to transform into long or wide form so I can do regression analysis. I have been trying to convert it, but am unlucky so far.
I would really appreciate if you would help me out with the conversion. The FY fields are the years (2010-2017).
Please find the excel file attached.
Thanks in advance.
You mean like this?
(you didn't show example result)
Thank you.
Please, see attached in excel file.
My dear friend, see your first attached file and create example result from data in this file
your second example has nothing common with the first (but maybe for you only)
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I have prepared the data as you asked. I have done it only for the first company just to give you an idea, the process is the same for other companies aswell placing the values of variables by time. I hope I made it easier for you
Is that what you want?
---
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
Last edited by sandy666; 09-01-2017 at 05:53 PM.
Yeah thats how I wanted it. Could you tell me how you have done it, so I can do it on large samples. Thanks!
1. don't use merged cells
2. use PowerQuery
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
here is example without any helper sheets, only source and result:
Last edited by sandy666; 09-02-2017 at 11:41 AM.
Thanks a alot! Really appreciate you helping me out.
Ok try with some formulas
A2Formula:
Please Login or Register to view this content.
B2Formula:Please Login or Register to view this content.
C2Formula:Please Login or Register to view this content.
All drag down.
D2=INDEX(Before!$C$3:$BF$10,MATCH($B2,Before!$B$3:$B$10,0),MATCH(D$1&$C2,INDEX(Before!$C$1:$BF$1&Before!$C$2:$BF$2,),0))
Copy across the all the cells till J65
Check the attached file.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
In the post #8 I attached wrong file.
Now should be ok.
Sorry for inconvenience
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks