+ Reply to Thread
Results 1 to 10 of 10

Transposing Layout (but also combining one value from numerous versions)

  1. #1
    Registered User
    Join Date
    02-09-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    8

    Transposing Layout (but also combining one value from numerous versions)

    I have this huge list of teachers who teach various subjects that I need to collate in a cleaner format. It is currently setup like this: (a teacher will teach up to 8 different courses so currently it will have 8 rows, one for each block.

    sample1.JPG

    I need it so that the teacher name get consolidated to ONE single row and then all of the other data gets aligned into that one row. The headings "Day1(1)", "Day2(2)", etc. will always be the top row.

    sample2.JPG

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transposing Layout (but also combining one value from numerous versions)

    It would help if you attached a sample Excel workbook, rather than pictures of one (which we can't play about with).

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon for attachments, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-09-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    8

    Re: Transposing Layout (but also combining one value from numerous versions)

    Sorry about that. Obviously should have actually attached a workbook! SHould be on this message. Thanks
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transposing Layout (but also combining one value from numerous versions)

    I used columns F and G as helper columns in the Source sheet, using these formulae:

    F2: =IF(COUNTIF(B$2:B2,B2)=1,MAX(F$1:F1)+1,"")

    G2: =LOOKUP(100,F$2:F2)*100+COUNTIF(B$2:B2,B2)

    and then copied these down to the bottom of your data.

    Then in Sheet2 I used this formula in A2:

    =IFERROR(INDEX(Source!$B:$B,MATCH(ROWS($1:1),Source!$F:$F,0)),"")

    and this one in B2:

    =IFERROR(INDEX(Source!$A:$A,MATCH(ROWS($1:1)*100+COLUMNS($B:B),Source!$G:$G,0)),"")

    The formula in B2 can then be copied across into C2:I2, and then the row of formulae can be copied down as far as you need them - I've just copied to row 6 in the attached file.

    Note that this depends on there always being 8 entries for each name, and that these are in the same sequence, so if a teacher did not teach in Day1(3), for example, that entry would still be there but there would be no subject.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-09-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    8

    Re: Transposing Layout (but also combining one value from numerous versions)

    Wow. Thank you so much. This is awesome. I do have a few follow-up requests to see if you can help out a little more.

    REQUEST #1:
    How can you modify the formula that pulls the name of the course and then make it actually pull the Room Number for another column. See Sheet2 for 8 new row headings I inserted.

    REQUEST #2:
    I'm not even sure if this is possible, but every teacher actually only teaches 7 periods. And it's completely random which one they are missing. For example, in this source, "Erin" does not have a class in Day2(4). "Jon" does not have one in Day1(3). And "Olivia" doesn't have a class in Day2(1). Is there any way to cross reference these "gaps" so that they don't show in the final output of Sheet2? If not, I suppose I will just manually add in a blank for the spots where they don't have a teaching block.

    Latest attachment is below.
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transposing Layout (but also combining one value from numerous versions)

    Change the formula in G2 of the Source sheet to this:

    =LOOKUP(100,F$2:F2)*100+MATCH(E2,Sheet2!$B$1:$I$1,0)

    then copy down - that will rectify the problem with missing teaching slots.

    You can get the rooms by putting this formula in K2 of the other sheet:

    =IFERROR(INDEX(Source!$C:$C,MATCH(ROWS($1:1)*100+COLUMNS($B:B),Source!$G:$G,0)),"")

    then copying across and down as required. Alternatively, you can show the room number in the other table by changing the formula in B2 to this:

    =IFERROR(INDEX(Source!$A:$A,MATCH(ROWS($1:1)*100+COLUMNS($B:B),Source!$G:$G,0))&CHAR(10)&INDEX(Source!$C:$C,MATCH(ROWS($1:1)*100+COLUMNS($B:B),Source!$G:$G,0)),"")

    Copy this across and down. You may need to adjust the row heights yourself, as Excel does not always set these automatically.

    I've shown both in the attached file, so you can decide which approach you like best.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-09-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    8

    Re: Transposing Layout (but also combining one value from numerous versions)

    Wow! You are IMPRESSIVE, Pete_UK. It's working amazingly well. Last 2 things I think.

    1) I should have mentioned that in the actual list there are over 100 teachers. So as soon as I get down to row 101, it stops working. I tried adjusting the formula but couldn't get it working.
    2) How would you setup conditional formatting so that any space that is a blank gets a light red shading?

    Latest version attached

    THank you!

    (you may also see there are a few random one-offs where the teaching period is designated as Day 1(8) or Day 1(5) or something. These designate courses outside of the standard timetable. I figured it would complicate things too much to have a column J on the output to do this. If you can think of an easy way to do it, let me know. Otherwise, I can manually adjust those ones.
    Attached Files Attached Files
    Last edited by guard23; 11-17-2019 at 02:57 PM.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transposing Layout (but also combining one value from numerous versions)

    I've set this up for you in the attached file. I don't have time right now to describe what I've done, as I'm just about to watch some TV, but I thought you might like to see the file. Post back if you have any questions.

    Pete
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-09-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    8

    Re: Transposing Layout (but also combining one value from numerous versions)

    WOW! Thanks so much!!!

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transposing Layout (but also combining one value from numerous versions)

    You're welcome, and thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

    P.S. If you do have any further questions about this, you might like to know that I'm going away tomorrow, so I won't be able to respond until Saturday or Sunday.

+ 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. [SOLVED] Copy Numerous Sheets to Numerous Unopened Workbooks
    By hchavous in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-23-2018, 04:51 PM
  2. Combining numerous dates into 12 median points
    By ablackburn21 in forum Excel General
    Replies: 3
    Last Post: 03-04-2016, 04:58 PM
  3. Combining and transposing data
    By curtrev in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2015, 01:07 PM
  4. Transposing list of values with 6 zeroes in front to retain the zero after transposing
    By Lisa4legin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2013, 03:34 AM
  5. [SOLVED] Excel 2007 : Combining Numerous Data From Many Cells Into One Cell
    By Rick K in forum Excel General
    Replies: 10
    Last Post: 10-06-2011, 01:02 PM
  6. Replies: 8
    Last Post: 08-23-2010, 05:28 PM
  7. Replies: 3
    Last Post: 10-08-2005, 03:05 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