+ Reply to Thread
Results 1 to 6 of 6

Transposing data

  1. 05-26-2020, 10:30 AM #1
    maxwelln1
    maxwelln1 is offline
    Registered User
    Join Date
    05-25-2018
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    11

    Transposing data

    Hello!

    Hoping someone can help point me in the right direction with this one, any ideas appreciated.

    I have an excel table, in row A is a list of companies. In row B, C, D, E, F are the top 5 investors of that company. What I'd like to do is transpose the data so that I have a list of investors in row A and the companies they've invested to in other columns.

    Before:
    Company Investor 1 Investor 2
    Google ABC Capital DEF Captial
    Microsoft GHI Capital DEF Capital

    After:
    Investors Company 1 Company 2
    ABC Capital Google
    DEF Capital Google Microsoft
    GHI Capital Microsoft

    Whats the best way to achieve this?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 05-26-2020, 12:59 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Transposing data

    Copy your Data

    Paste Special Transpose.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  3. 05-26-2020, 01:12 PM #3
    maxwelln1
    maxwelln1 is offline
    Registered User
    Join Date
    05-25-2018
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    11

    Re: Transposing data

    Thank you for the suggestion but the button is greyed out for me. Additionally, I am expecting it to combine all the investor columns together as in my example above. As far as I know (from what I read about transposing) is that it is designed for one row to become one column, not for combining. Is that correct?
    Reply With QuoteRegister To Reply
  4. 05-28-2020, 05:59 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Transposing data

    This proposal combines Get & Transform with formulas
    Step 1: Convert the Before range to a table.
    Step 2: Utilize Power Query to transform the table using the following advanced power query editor code:
    Please Login or Register  to view this content.
    Step 3: Use the following formula to concatenate the company terms: =IF(E2<>E1,F2,G1&","&F2)
    and the following to identify those rows to be indexed: =IF(E2<>E3,1,"")
    Step 4: Use the following formula to output the investors: =IFERROR(INDEX(Table1_2[Investor],AGGREGATE(15,6,(ROW(Table1_2[Investor])-ROW(Table1_2[#Headers]))/(H$2:H$5=1),ROWS(A$1:A1))),"")
    and the following to output the companies:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    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.
    Reply With QuoteRegister To Reply
  5. 06-01-2020, 07:06 AM #5
    maxwelln1
    maxwelln1 is offline
    Registered User
    Join Date
    05-25-2018
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    11

    Re: Transposing data

    Wow! Thank you JeteMC this is exactly what I was hoping to achieve! Thank you very much indeed. Also really helpful to have the workbook example you attached, makes it easier to understand the steps.

    You're a star!
    Reply With QuoteRegister To Reply
  6. 06-01-2020, 06:48 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Transposing data

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Transposing Vertical data to Horizontal data without pivot table
    By Ian.Lance in forum Excel General
    Replies: 5
    Last Post: 05-15-2020, 09:11 AM
  2. Transposing repeating vertical data to horizontal data but retaining unique values only
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 12:57 AM
  3. 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
  4. transposing data
    By kate1234 in forum Excel General
    Replies: 6
    Last Post: 08-14-2012, 05:58 PM
  5. Excel 2007 : Converting horizontal data to vertical data (more than just transposing)
    By glens73 in forum Excel General
    Replies: 5
    Last Post: 04-23-2010, 08:01 AM
  6. [SOLVED] transposing vertical data to horizontal with varying amount of data
    By Ghosty in forum Excel General
    Replies: 5
    Last Post: 08-01-2006, 12:23 AM
  7. Transposing Data
    By Amy_SATX in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:28 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1