+ Reply to Thread
Results 1 to 10 of 10

I'm stuck trying to pivot a column and cross reference two tables!

  1. #1
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13

    I'm stuck trying to pivot a column and cross reference two tables!

    Hi All,

    I haven't posted in ages so sorry if i miss any etiquette. Shout at me and i will sort it out!
    I am struggling with a problem that i am going to struggle to explain. I have attached a file that should show what i need.

    I have two tables of employees. One has their skills. There is one row per skill. (If an employee has five skills --- five rows)
    The other shows there roster. There is one row per person per day. (I normally am working with 28 days of data)
    There are loads of other fields including unique employee numbers.
    I can pivot the skills table to give me one row per person and one skill per column and a 1 in the box for the skills they have. (This sentence is a mess but it is shown in the file and should make sense.)

    I basically want the pivoted table (as a table not a pivot) merged with the roster table so i can then filter to show what skills i have on what day.

    My data regularly changes so i cant do any of this manually!

    Any help is most appreciate!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Here is a Power Query Solution. Power Query or Get and Transform in Excel 365 is found on the Data Tab.

    Mcode:

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Name Number - Copy Driving IT Singing
    2
    Dave
    1
    1
    1
    1
    3
    Fred
    3
    1
    0
    0
    4
    Jane
    2
    1
    0
    0
    Sheet: Sheet2

    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files
    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

  3. #3
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Hi Alan,

    Thanks so much for this.

    I don't think i have explained myself very well I also need the other table incorporated so i end up with one row per date with ll of the skills on each day.

    The result will look something like this;



    Attachment 693050

    Then i will be able to see what skills i have available on each day?

    Just to complicate this - if possible i am trying to avoid power query as some of our computers are running older versions of excel.
    If it is my only solution then i will run with it but it can be avoided then ideally it would be.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Quote Originally Posted by tommo385 View Post
    some of our computers are running older versions of excel.
    So precisely which version of Excel does any solution need to be compatible with?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13
    Quote Originally Posted by XOR LX View Post
    So precisely which version of Excel does any solution need to be compatible with?

    Regards
    Ah good question. I would have to go to each site to work that out, however, I am now thinking with the above solution I could maybe merge the tables with a second query. If the query's are run on a compatible pc then the data could be utilised on other versions.

  6. #6
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Ignore my whining about Excel versions. I have worked it out that i can use Power Query.


    On that basis i am looking now to merge the two tables so i can see the skills for each person on each day.

    I have tried a merge query but it creates one row per skill per day resulting in 120000 rows instead of about 50000.


    Desired result is attached.

    Any help would be most appreciated.
    Attached Files Attached Files
    Last edited by tommo385; 09-01-2020 at 11:30 AM.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    Sorted. Thanks so much.

    I really need to learn power query.

    Thanks

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: I'm stuck trying to pivot a column and cross reference two tables!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

    If you are truly interested in learning PQ, then "M is for (Data) Monkey" by Ken Puls and Miguel Escobar is a good starting point.

  10. #10
    Registered User
    Join Date
    01-16-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    13
    Quote Originally Posted by alansidman View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

    If you are truly interested in learning PQ, then "M is for (Data) Monkey" by Ken Puls and Miguel Escobar is a good starting point.
    Thanks so much. I will look in to it. Cheers.

+ 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. Creating cross-tabs with pivot tables
    By nickbarthram in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-20-2015, 06:22 AM
  2. Cross Reference Two X-Y Tables that share the X Axis
    By lucidus in forum Excel General
    Replies: 0
    Last Post: 10-19-2012, 12:11 PM
  3. Cross Reference Data Tables to one tab for parts list
    By Jamers in forum Access Tables & Databases
    Replies: 0
    Last Post: 07-19-2012, 12:58 PM
  4. Reference Pivot Tables Grand Total Column
    By Hat Man in forum Excel General
    Replies: 0
    Last Post: 06-21-2011, 08:08 PM
  5. Replies: 4
    Last Post: 04-26-2011, 04:38 PM
  6. cross reference data tables
    By almac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2011, 10:56 PM
  7. cross reference data tables
    By almac in forum Excel General
    Replies: 1
    Last Post: 03-18-2011, 10:34 PM

Tags for this Thread

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