Hi guys,
Needed help with the attached. I essentially have this data from line B-W and I need to transpose it in a way to make it into a table like the picture in the file. The picture in the file is an example of the first month of 3. as you can see 3 months has 4 different ways to earn and i have manually copy and pasted those into the picture. There must be a way to make this easier then just copy and pasting? any help is appreciated!
Hi
what is the office version you are using?
Hi I am using office professional plus 2016
Perhaps this will help.
1. Populate C1:W1 using: =COUNT(C6:C126)
2. Populate C2:W2 using: =SUM(B2,C1)
3. Populate a reference column using: =INDEX(C$5:W$5,AGGREGATE(15,6,(COLUMN(C$5:W$5)-COLUMN(B$5))/(C$2:W$2>=ROWS(Z$3:Z3)),1))
Note that the above may be moved and/or hidden for aesthetic purposes.
4. Populate the Months column using: =IFERROR(LEFT(Z3,SEARCH("/",Z3)-1)+0,Z3)
5. Populate the Term Milage column using: =IFERROR(MID(Z3,SEARCH("/",Z3)+1,IF(RIGHT(Z3,1)="K",SEARCH("K",Z3)-SEARCH("/",Z3)-1,3))*IF(ISNUMBER(SEARCH("K",Z3)),1000,1),"")
6. Populate the Expired Months column using: =COUNTIFS(Z$3:Z3,Z3)-1
7. Populate the ITD Earned Factor column using: =INDEX(C$6:W$126,MATCH(AC3,B$6:B$126,0),MATCH(Z3,C$5:W$5,0))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Please update your profile as it indicates you are using 2013. The version you are using will dictate the solution.Hi I am using office professional plus 2016
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks