+ Reply to Thread
Results 1 to 6 of 6

Excel transpose

  1. #1
    Registered User
    Join Date
    09-13-2015
    Location
    Bengaluru, KA, India
    MS-Off Ver
    Home & Student 2021
    Posts
    36

    Post Excel transpose

    Sir,

    I have been facing this peculiar problem to transpose the data in excel. I have used INDEX, MATCH and even V LOOKUP, but couldn't find a feasible solution.
    Please assist.

    I have attached the requisite file.
    Attached Files Attached Files
    Last edited by avinash21; 11-27-2019 at 03:55 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel transpose

    In C8, a helper, copied down:

    =IF(B8="",I7,B8)

    In K5, copied to L5 & down:
    IFERROR(INDEX(B$8:B$30,MATCH(0,INDEX(COUNTIF(K$3:$K4,B$8:B$30&""),),0)),"")

    In m5, with similar formulae in P &S
    =IFERROR(INDEX($F$8:$F$30,MATCH(1,INDEX(($I$8:$I$30=$K5)*($E$8:$E$30=100*M$3),0),0)),"")

    The formulae in the amount cells need an error statement:
    =IFERROR($M5*($M$3/2),"")

    Only done for column N.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-13-2015
    Location
    Bengaluru, KA, India
    MS-Off Ver
    Home & Student 2021
    Posts
    36

    Re: Excel transpose

    Brother Glenn Kennedy ,
    I have copied the formula from K5 to L5. but, it shows in-correct result when L4 & L5 as same values after this, the results in C & L column do not match.
    Kindly assist.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel transpose

    I should have said dragged, not copied. Beyond that, i can not diagnose the problem if I can not see what you have done.

    Do you accept that it was working the the sheet that I posted?

  5. #5
    Registered User
    Join Date
    09-13-2015
    Location
    Bengaluru, KA, India
    MS-Off Ver
    Home & Student 2021
    Posts
    36

    Thumbs up Re: Excel transpose

    It's okay brother. I have applied the formula only to numerical & Excel has done a great job here (and not to names). I'm very thankful to you for getting the work done.
    Consider this thread as solved.

    Thank you once again!

    -With Love from India.
    Last edited by avinash21; 11-27-2019 at 05:44 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel transpose

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. To transpose or not to transpose? What decides? Table / Array?
    By LIL2606 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-19-2019, 01:28 PM
  2. Replies: 4
    Last Post: 08-21-2018, 09:24 AM
  3. [SOLVED] Use the TRANSPOSE() function to transpose a range as an input to the UDF
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2015, 09:35 PM
  4. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM
  5. [SOLVED] Transpose: How can I copy a list and transpose it but leaving 3 cells in between each item
    By cocolete in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-11-2012, 10:01 AM
  6. Using Transpose in excel
    By ageless in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-17-2009, 03:38 PM
  7. Replies: 2
    Last Post: 02-19-2007, 04:53 PM

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