+ Reply to Thread
Results 1 to 16 of 16

Excel - Changing row & column to match training completed dates by each individual

  1. #1
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Excel - Changing row & column to match training completed dates by each individual

    Hi all,

    I require a help and advice on excel training matrix. As English is not my native language I might not be able to explain fully but please try to understand.

    I attached the file (TEMPLATE 1) for your review. Currently, all employees in rows and training, completed date, & employee ID numbers are listed in columns next to each other.

    I want to accomplish:

    A
    1. Employees in row (without duplication) + 2. All trainings in column + 3. completed dates to match each individual and by training completed (not all employees completed same training and different completion date!!! )

    B

    Possible to color counting on this?

    I have tried to accomplish above with pivot tables, and filters but it doesn't work as I desired.

    To give you a better understanding, I have attached a sample template report (TEMPLATE 2) - I want to TEMPLATE 1 to look similar to this, if possible.

    I am sure there must be a way/method to do this. Otherwise it will take hours and lots of work to manually input this .

    Hope some will be able to help and advise. Much appreciated and thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    Perhaps this will be a starting place.
    This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
    The first helper column (CK) is populated using: =COUNT(E7:CJ7)
    The second helper column (CL) is populated using: =SUM(CL6,CK7)
    The Name column is populated using: =INDEX(D$7:D$250,AGGREGATE(15,6,(ROW(D$7:D$250)-ROW(D$6))/(ROWS(A$1:A1)<=CL$7:CL$250),1))
    The Training column is populated using: =INDEX(E$5:CJ$5,AGGREGATE(15,6,(COLUMN($E$6:$CJ$6)-COLUMN($D$6))/($D$7:$D$250=$CN7)/($E$7:$CJ$250<>"")-1,COUNTIFS($CN$7:$CN7,$CN7)))
    The Completed column is populated using: =INDEX(E$7:CJ$250,MATCH(CN7,D$7:D$250,0),AGGREGATE(15,6,(COLUMN($E$6:$CJ$6)-COLUMN($D$6))/($D$7:$D$250=$CN7)/($E$7:$CJ$250<>""),COUNTIFS($CN$7:$CN7,$CN7)))
    Note that in the Training column a zero is displayed. Tracing that back it occurs because the date is placed in a "Non Compliant..." column. I am not sure if you want those Training's to be included, so I have stopped at this point so that a decision can be made as to how you wish to proceed.
    I don't know to what the Number column in template 2 refers.
    At present the range only extends through row 515 to see the full list copy the formulas in cells CN515:CP515 down through row 3764.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    Hi JeteMc,

    Thanks for taking time to go through my post. Much appreciated for your input, tho what you put wasn't exactly what I asked .

    Template 1 is just a sample - no need to do anything on it. But Template 2 is what I need to customize to look similar to Template 1.

    Temp 2 contains all employees' completed training and completed dates. As you can see, the employee names in row & training names are duplicate in column for each training. I want to customize in a way: A) list all employees in row without duplicates (easy to do bu removing duplicates but that will delete training & completed dates) B) match each training & completed dates to match employee.

    I have attached a sample I made for only the first 2 employees (by the way I had to do this manually by copy + paste) I want Template 2 to look exactly like Template 3. I am sure there are ways to do it automatically for all. Pivot table just makes it very hard to understand does not really show what I am looking for.

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    See if the following is what you want.
    On the Template 3 sheet the names are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The dates are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    This works. But I really want to understand how you did it, how you do this, how you know which formula to use and use range data? At the moment, I cant clearly understand what's going on...

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    I have placed some new sheets in the workbook. The new sheets contain smaller versions of templates 2 and 3. I feel that a good way to learn what a formula does is to use the Evaluate Formula feature. I suggest that you select cell A6 on the Small sample of output sheet and run the feature. I would also suggest running it for A8 (so you can see the IFERROR function work) as well as B6 and S7.
    Once satisfied, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
    Let us know if you need help understanding any of the steps in the evaluation, or if you have any questions.

  7. #7
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    Quote Originally Posted by JeteMc View Post
    I have placed some new sheets in the workbook. The new sheets contain smaller versions of templates 2 and 3. I feel that a good way to learn what a formula does is to use the Evaluate Formula feature. I suggest that you select cell A6 on the Small sample of output sheet and run the feature. I would also suggest running it for A8 (so you can see the IFERROR function work) as well as B6 and S7.
    Once satisfied, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
    Let us know if you need help understanding any of the steps in the evaluation, or if you have any questions.
    Ummm where is this new small workbook you speak? Maybe u forgot? :P

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    Sorry about that.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    Quote Originally Posted by JeteMc View Post
    Sorry about that.
    I have been playing with the new small sample and read your instruction word by word, and used evaluate formula option and still have no clue. I am not a retard and I think I am normal user of computer and MS office products but understand absolutely nothing from all these advanced/pro formula you use and how it works.

    Do you think you can do the same to this attached workbooks (multiple tabs) please?????
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-08-2016
    Location
    kuala lumpur malaysia
    MS-Off Ver
    365
    Posts
    100

    Re: Excel - Changing row & column to match training completed dates by each individual

    Hi Byambadorj

    I am using this template at "Small sample of output" by adding data in "Small sample of original data".
    what I have done, I am adding "Anand Tsevelmaa" until row 33. Then after, I change the formula in "Small sample of output" from 27 to 33.
    It is working.

    On how to change 27 to 33, I am using Ctrl+F and replace 27 (looking value in formula) to 33.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    If you have been using pivot tables and haven't had the need of these kinds of formulas before they seem complicated. Actually it would seem that pivot tables that you mention in post #1 should do what I have done, so I am a bit curious as to why they don't work for your purposes. Perhaps you could upload a pivot table to demonstrate what is going on and we could work on getting it to show what you want and eliminate the formulas.
    I have added output tables to each of the three sheets in the AQ Training Matrix 2 workbook. See if these are what you want and if so I will put the output tables on the two sheets of the other workbook tomorrow.
    Let us know if you have any questions.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    Quote Originally Posted by JeteMc View Post
    If you have been using pivot tables and haven't had the need of these kinds of formulas before they seem complicated. Actually it would seem that pivot tables that you mention in post #1 should do what I have done, so I am a bit curious as to why they don't work for your purposes. Perhaps you could upload a pivot table to demonstrate what is going on and we could work on getting it to show what you want and eliminate the formulas.
    I have added output tables to each of the three sheets in the AQ Training Matrix 2 workbook. See if these are what you want and if so I will put the output tables on the two sheets of the other workbook tomorrow.
    Let us know if you have any questions.
    OMG, it's exactly what I was looking for. How did you do this (I can see the formulas that you used) but how do you know which formula to use and works?? Can this (what you have done with formula) be done with pivot table? If so can you please show me?

    Can you do the other 2 sheets of other workbook? You are just amazing!

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    I believe that I found the answer to my question about why the pivot table option didn't work. The values in the Qualification Expiry Date column are text and not dates. I used the Text to Columns feature to convert the text to dates then formatted that column dd.mm.yyyy so that it appears as it did originally. Having done that a pivot table can be produced as modeled in the ALL PT, HME PT and SWP PT sheets.
    I will take a look at the other workbook and see if those sheets can be formatted in such a way as to produce pivot tables.
    Let us know if you have any questions.
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    The Mandatory PT sheet is a little more complicated in that I assume you only want to see dates related to Compliant Qualification Expiry. To accomplish that Status needs to be placed in the filters field. I also assume that you want the Mandatory / Essential dates displayed. The pivot table for the SAPBW_DOWNLOAD history PT is pretty simple as the dates on that sheet were actual dates so no conversion is necessary.
    Let us know if you have any questions.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Excel - Changing row & column to match training completed dates by each individual

    Quote Originally Posted by JeteMc View Post
    If you have been using pivot tables and haven't had the need of these kinds of formulas before they seem complicated. Actually it would seem that pivot tables that you mention in post #1 should do what I have done, so I am a bit curious as to why they don't work for your purposes. Perhaps you could upload a pivot table to demonstrate what is going on and we could work on getting it to show what you want and eliminate the formulas.
    I have added output tables to each of the three sheets in the AQ Training Matrix 2 workbook. See if these are what you want and if so I will put the output tables on the two sheets of the other workbook tomorrow.
    Let us know if you have any questions.
    On the formula used spreadsheet, how can I use that formula on other workbook or sheet to select the data range? It's not working. Can you show me how it's done on another workbook - using formula, not pivot tables? Thank you.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Excel - Changing row & column to match training completed dates by each individual

    The formulas have been applied to the other file.
    Two ways to see what the formulas do:
    1. Select the cell and press the F2 key to see color coded cells and columns that are being referenced by the formulas.
    2. Select the cell and use the Evaluate Formula feature found on the Formulas tab.
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-18-2017, 08:43 AM
  2. [SOLVED] Table that will show if person has completed particlar training
    By TAMMY32 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-02-2017, 06:28 PM
  3. Working with excel to track training and expiry dates
    By Trainray in forum Excel General
    Replies: 2
    Last Post: 04-29-2016, 03:35 PM
  4. Traffic light individual cell based on training dates due
    By jjscaramanga in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-28-2015, 06:28 AM
  5. Replies: 0
    Last Post: 11-02-2011, 08:47 PM
  6. Excel 2007 : Training Tracker and Due Dates
    By InNeedoHelp in forum Excel General
    Replies: 2
    Last Post: 10-27-2009, 02:52 AM
  7. [SOLVED] Resize individual cells w/o changing the entire column width
    By Mary Ann in forum Excel General
    Replies: 3
    Last Post: 04-28-2006, 11:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1