+ Reply to Thread
Results 1 to 9 of 9

Transposing columns into new rows.

  1. #1
    Registered User
    Join Date
    03-04-2019
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    3

    Post Transposing columns into new rows.

    Hello,

    I am running into some trouble trying to convert my data into a usable format. I have a site name in column A followed by IP addresses in columns afterwards ranging from 1 to hundreds of columns. I need this data to be transposed onto new rows instead of columns. I am having a hard time explaining my end goal but have attached a example of what I am trying to accomplish. any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by ensio0651; 03-04-2019 at 01:17 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,877

    Re: Transposing columns into new rows.

    Hi & welcome to the board.
    How about
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,672

    Re: Transposing columns into new rows.

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Registered User
    Join Date
    03-04-2019
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    3

    Re: Transposing columns into new rows.

    Apologies not the most competent when it comes to running code in excel could you tell me how to use the code.

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,672

    Re: Transposing columns into new rows.

    Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,877

    Re: Transposing columns into new rows.

    Have a look here https://www.contextures.com/xlvba01.html
    The code needs to go in a regular module.

    Also with my code you will need to create a new sheet called Output

  7. #7
    Registered User
    Join Date
    03-04-2019
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    3

    Re: Transposing columns into new rows.

    Thank you I got it working you are a life saver!

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

    Re: Transposing columns into new rows.

    You can do it with a few formulae, as the attached file demonstrates, using these formulae in the cells stated on the Desired sheet:

    A2: =COUNTA(Current!B2:Y2)

    B2: =A2+B1

    It is imporatant to put zero in cell B1. The first formula gives a count of the number of IP addresses in the Current sheet - you might need to change the Y to some other column, sufficient to cover the maximum number of IP addresses that you have. The second formula gives a cumulative count of the numbers.

    Copy these two formulae down until you start to get zeros in column A (this depends on how many entries you have in the Current sheet). Then put these formulae in the cells stated:

    D2: =IF(ROWS($1:1)>MAX(B:B),"-",MATCH(ROWS($1:1)-1,B:B)+1)

    E2: =IF(OR($D2="",$D2="-"),"",INDEX(Current!A:A,$D2))

    F2: =IF(OR($D2="",$D2="-"),"",INDEX(Current!B:Y,$D2,COUNTIF(D$2:D2,D2)))

    Again, you might need to change the Y parameter, as advised above. Copy these three formulae down until you start to get hyphens in column D.

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,877

    Re: Transposing columns into new rows.

    Quote Originally Posted by ensio0651 View Post
    Thank you I got it working you are a life saver!
    Glad we could help & thanks for the feedback

+ 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. Transposing certain rows into columns
    By alidaanish in forum Excel General
    Replies: 3
    Last Post: 01-15-2019, 05:51 PM
  2. Replies: 1
    Last Post: 07-20-2017, 01:52 PM
  3. Replies: 2
    Last Post: 07-20-2017, 11:50 AM
  4. Need help transposing rows into columns
    By mckeven in forum Excel General
    Replies: 4
    Last Post: 10-08-2014, 04:41 PM
  5. [SOLVED] Transposing 2 - 70,000+ long columns into approx 4600 rows with 15 columns each
    By Glennstapo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 09:51 AM
  6. Replies: 8
    Last Post: 11-02-2011, 02:45 PM
  7. Transposing columns to rows
    By sunitagadapu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2009, 08:54 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