+ Reply to Thread
Results 1 to 21 of 21

Selective transpose formula or similar

  1. #1
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Selective transpose formula or similar

    Hi

    Excel newbie here! I'd appreciate any assistance with the following:

    I'm trying to selectively transpose a row of data to a column. Please see my attachment.

    I need know how to typically do the following:

    Example:
    A1 B1
    (needs to transpose to)
    A3 B3

    C1 D1
    (needs to transpose to)
    A4 B4

    E1 F1
    (needs to transpose to)
    A5 B5

    (etc)

    I'm using Excel 2019
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Selective transpose formula or similar

    In A3

    =INDEX($A$1:$H$1,,(ROWS($1:1)-1)*2+1)

    in B3

    =INDEX($A$1:$H$1,,ROWS($1:1)*2)

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

  3. #3
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Thank you!

    That's definitely what I need

    It works in the example I provided but I'm having a problem implementing it.

    In the attached example the values display backwards.

    I'd appreciate any suggestions.

    In the Report sheet
    First column
    =INDEX('Progression Schedule'!$H$11:$AG$11,,(ROWS('Progression Schedule'!1:$11)-1)*2+1)

    Second Column
    =INDEX('Progression Schedule'!$H$11:$AG$11,,(ROWS('Progression Schedule'!1:$11)-1)*2+1)
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Selective transpose formula or similar

    Another one way formula, takes care of both numbers from the first example.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Selective transpose formula or similar

    Quote Originally Posted by w china View Post
    In the Report sheet
    First column
    =INDEX('Progression Schedule'!$H$11:$AG$11,,(ROWS('Progression Schedule'!1:$11)-1)*2+1)

    Second Column
    =INDEX('Progression Schedule'!$H$11:$AG$11,,(ROWS('Progression Schedule'!1:$11)-1)*2+1)
    Second Column
    =INDEX('Progression Schedule'!$H$11:$AG$11,,ROWS('Progression Schedule'!1:$11)*2)

  6. #6
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Apologies- mistake in the formula example. Incorrect sheet name.

    I meant...

    In the Report sheet
    First column
    =INDEX('Marks'!$H$11:$AG$11,,(ROWS('Marks'!1:$11)-1)*2+1)

    Second Column
    =INDEX('Marks'!$H$11:$AG$11,,(ROWS('Marks'!1:$11)-1)*2+1)
    Last edited by w china; 11-25-2021 at 03:50 AM.

  7. #7
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi thanks for that

    Unfortunately it only works for the first two values.

    =OFFSET($A$1,0,(ROWS($1:1)-1)*2,1,2)

    How would I implement for the whole row?

  8. #8
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Selective transpose formula or similar

    Correction keeps the same for second column

    Second Column
    =INDEX('Marks'!$H$11:$AG$11,,ROWS('Marks'!$1:1)*2)
    Last edited by DJunqueira; 11-25-2021 at 04:05 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Selective transpose formula or similar

    First column
    =INDEX('Marks'!$H$11:$AG$11,,(ROWS($1:1)-1)*2+1)

    Second Column
    =INDEX('Marks'!$H$11:$AG$11,,ROWS($1:1)*2

    'Progression Schedule' not in your sample workbook !

    The highlighted ROWS calculation is a simple count to determine the column in the INDEX formula so has no need to reference a sheet.
    Attached Files Attached Files
    Last edited by JohnTopley; 11-25-2021 at 07:06 AM.

  10. #10
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi John

    Apologies for the error in my example sheet name.

    First column works with:
    =INDEX(Marks!$H$11:$AG$11,,(ROWS($1:1)-1)*2+1)

    Second column works with:
    =INDEX(Marks!$H$11:$AG$11,,ROWS(1:1)*2)

    However copy down doesn't work (see attached).
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,408

    Re: Selective transpose formula or similar

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    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.

  12. #12
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Selective transpose formula or similar

    One formula solution applied on your exemple.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Selective transpose formula or similar

    What doesn't work ??? the results are as per !Marks"

    They would if you manage to copy the formula correctly !!!

    =INDEX(Marks!$H$11:$AG$11,,ROWS($1:1)*2)
    Last edited by JohnTopley; 11-25-2021 at 05:04 AM.

  14. #14
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi John, thanks for your help again.

    Excel keeps telling me there is a typo in your column 2 formula, however I've tried both: your exact formula and excel's 'corrected' version.

    Both produce the same result. The first entry is correct, but copying down does not produce the other results.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,408

    Re: Selective transpose formula or similar

    I am guessing that you removed one of these commas:

    =INDEX(Marks!$H$11:$AG$11,,ROWS($1:1)*2)

    If you did, then no dice!

  16. #16
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi Junqueira

    Thanks for your solution.

    It works for the first entry, however if I copy down it does not how the other results correctly.

  17. #17
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi AliGW

    Thanks for the suggestion.

    I've double checked using that formula. The first entry is correct, however the rest of the data in the row does not transfer correctly into the column in the manner:

    Example:
    A1 B1
    (needs to transpose to)
    A3 B3

    C1 D1
    (needs to transpose to)
    A4 B4

    E1 F1
    (needs to transpose to)
    A5 B5

    (etc)

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Selective transpose formula or similar

    How many times do I have to repeat myself????: see attached.
    Attached Files Attached Files
    Last edited by JohnTopley; 11-25-2021 at 07:14 AM.

  19. #19
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Hi John

    Thank you again for your assistance.

    I've copied the code over from your spreadsheet and it's working now.

    I'm still not sure what the issue was, as I checked (double, triple) the copying over of your formulas. Perhaps a version issue?

    But regardless, thank you for your patience and making my job as a teacher easier!

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Selective transpose formula or similar

    It is not a version issue: simple copy/paste of the formulae should have sufficed.

    Anyway, glad it is resolved so can you mark this as SOLVED (see "Thread Tools" at top of the thread).

    Thank you.

  21. #21
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Selective transpose formula or similar

    Thread solved (already done)
    Copy paste - I did exactly that, plus AB comparison in notepad. Puzzling.
    Thank you again for assisting me.

+ 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] Transpose similar IDs in single row
    By KingTamo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2017, 07:03 AM
  2. [SOLVED] Selective Transpose Multiple Rows of Data to Columns
    By MileHigh_PhD in forum Excel General
    Replies: 14
    Last Post: 07-22-2013, 01:15 PM
  3. Selective Transpose
    By rkgautam in forum Excel General
    Replies: 4
    Last Post: 05-30-2013, 02:27 PM
  4. Issue similar to Transpose with a twist
    By simple2smilee in forum Excel General
    Replies: 0
    Last Post: 06-22-2011, 12:29 PM
  5. Selective transpose in Excel through VBA
    By cbase in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2007, 10:58 AM
  6. address help (similar to trim or transpose)
    By Aaaaa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2006, 11:20 AM
  7. Transpose-Is there a similar limitation for XP?
    By ojv in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2005, 09:06 AM

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