Hello,
Just trying to organize my dataset to where it is one long list of data instead of having it organized in the table.
In the sheet Indy GP T I have a table with the lap in column 1, then people listed across the top and their positions listed below.
I want to create a list with Columns: Driver, Lap and Position. I have formatted how I would like to see it in the other sheet. But unsure (besides manually) how to generate the list.
Thank you
This can be done a few ways. The most straight forward is to use Power Query. The attached workbook includes that solution.
Perfect, thank you.
Could you please walk me through how you generated the list?
Best.
This updated page also has the VBA solution that arrives at the same solution. In this case VBA creates an output sheet. This update contains both solutions.
Please Login or Register to view this content.
Last edited by maniacb; 07-03-2020 at 12:13 PM. Reason: Corrections to response
Open Power Qwery (get Data) under data, select entire table, choose select table, once in PQ, right click the top left hand column and select "unpivot other columns" Close and save. I hope that works for you.
I appreciate the help. Looks like i need to learn some more about how to do this because I'm still a bit lost.
Thank you again
I think it also has to do with Excel on Mac does not have Power Query. Oh well
@Maniacb
When offering coded solutions, either M Code or VBA, please post the relevant code to the thread for completeness. Thanks.
Unfortunately. PQ solutions are not appropriate for Excel for Mac users as it’s not supported In that version.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Not sure if you were able to leverage the vba code above, so I included a formula solution to come up with the same list. The formula are as follow
indy.png
at E90
=IFERROR(INDEX($A$2:$A$87,AGGREGATE(15,6,(ROW($A$2:$A$87)-ROW($A$2)+1)/($B$2:$Y$87<>""),ROWS(E$90:E90))),"")
at F90
=IFERROR(INDEX($B$1:$Y$1,AGGREGATE(15,6,(COLUMN($B$1:$Y$1)-COLUMN($B$1)+1)/($B$2:$Y$87<>"")/($A$2:$A$87=E90),COUNTIFS(E$90:E90,E90))),"")
at G90
=XLOOKUP(E90,A$1:$A$87,XLOOKUP(F90,$A$1:$Y$1,($A$1:$Y$87)))
and copy down about 2000 lines
Last edited by maniacb; 07-03-2020 at 06:13 PM. Reason: Update Picture
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks