+ Reply to Thread
Results 1 to 10 of 10

How to convert or transpose list into table?

  1. 10-31-2014, 01:28 PM #1
    effluvium
    effluvium is offline
    Registered User
    Join Date
    10-31-2014
    Location
    Vancouver
    MS-Off Ver
    2010
    Posts
    4

    How to convert or transpose list into table?

    Hi there -- am struggling with converting a automated report of network switch stack members into a table. The stack size varies and could contain 1 to 6 units while the other formatting is consistent.

    Example <snippet of actual output>:

    ABBMcHSW01 (10.1.1.253):

    --------------
    dis device manuinfo
    --------------
    Unit 1
    First mac address : 0016-e01f-0840
    Product serial number: YEFF6UH1F0840
    Product 3C number : 3CR17572-91
    Unit 2
    First mac address : 0016-e0d1-3ac0
    Product serial number: YEFF75HD13AC0
    Product 3C number : 3CR17572-91


    ABIPBMTSW01 (10.1.1.121):

    --------------
    dis device manuinfo
    --------------
    Unit 1
    First mac address : 001e-c1bf-6140
    Product serial number: 9KXF9XMBF6140
    Product 3C number : 3CR17172-91


    ABIPCORESW01 (10.1.1.122):

    --------------
    dis device manuinfo
    --------------
    Unit 1
    First mac address : 001e-c1bf-5f00
    Product serial number: 9KXF9XMBF5F00
    Product 3C number : 3CR17172-91


    Desired Outcome:

    ABBMcHSW01 (10.1.1.253): Unit 1 Product serial number: YEFF6UH1F0840
    ABBMcHSW01 (10.1.1.253): Unit 2 Product serial number: YEFF75HD13AC0
    ABIPBMTSW01 (10.1.1.121): Unit 1 Product serial number: 9KXF9XMBF6140
    ABIPCORESW01 (10.1.1.122): Unit 1 Product serial number: 9KXF9XMBF5F00


    Any suggestions on how to tackle this? If this is outside the scope of what Excel would normally do pls let me know.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 10-31-2014, 03:22 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to convert or transpose list into table?

    This would require VBA programming, you may not be aware of the "automation" capabilities inside Excel.

    If you can provide an example or two of the raw text files or the raw Excel files you start with, I can write a little routine and add it to a "button" to make it easy on you.

    If they are text files, a few questions:

    1) what is the folder path to where you store those text files?
    2) Should the macro "clear" the table and make a clean new one each time, or add to the bottom of any existing data in the table?


    To attach a sample workbook and text files - Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Last edited by JBeaucaire; 10-31-2014 at 03:27 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 10-31-2014, 04:46 PM #3
    effluvium
    effluvium is offline
    Registered User
    Join Date
    10-31-2014
    Location
    Vancouver
    MS-Off Ver
    2010
    Posts
    4

    Re: How to convert or transpose list into table?

    NCMJobCompletionV1.xls

    Thank you Jerry -- I was aware of but familiar enough to use macros or vb within Excel. I believe the excel file is attached. Adding to the bottom of existing data would be perfect. Much appreciated.
    Reply With QuoteRegister To Reply
  4. 10-31-2014, 05:13 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to convert or transpose list into table?

    The resulting table is a single string for each unit? Or is that separate cells in a row? Your sheet2 didn't have a mockup of what you really want to see.
    Reply With QuoteRegister To Reply
  5. 10-31-2014, 05:47 PM #5
    effluvium
    effluvium is offline
    Registered User
    Join Date
    10-31-2014
    Location
    Vancouver
    MS-Off Ver
    2010
    Posts
    4

    Re: How to convert or transpose list into table?

    Ideally the resulting table would have separate cells in a row something like this:

    Please Login or Register  to view this content.
    where the information in Column A would be repeated if there was more than one switch in the stack

    Struggling a bit with the smartest way to represent the data in the report. Once it is like this folks where I work with average Excel skills should be able to manipulate easier...
    Last edited by JBeaucaire; 10-31-2014 at 06:38 PM.
    Reply With QuoteRegister To Reply
  6. 10-31-2014, 06:41 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to convert or transpose list into table?

    How about this?
    Data Range
    A
    B
    C
    D
    1
    Device
    IP
    Unit
    Serial Number
    2
    ABBMcHSW01
    10.1.1.253
    1
    YEFF6UH1F0840
    3
    ABBMcHSW01
    10.1.1.253
    2
    YEFF75HD13AC0
    4
    ABIPBMTSW01
    10.1.1.121
    1
    9KXF9XMBF6140
    5
    ABIPCORESW01
    10.1.1.122
    1
    9KXF9XMBF5F00
    Reply With QuoteRegister To Reply
  7. 10-31-2014, 07:14 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to convert or transpose list into table?

    The macro:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-03-2014, 03:03 PM #8
    effluvium
    effluvium is offline
    Registered User
    Join Date
    10-31-2014
    Location
    Vancouver
    MS-Off Ver
    2010
    Posts
    4

    Re: How to convert or transpose list into table?

    Spot on! Will spend some time to figure out your macro. Thanks for your effort on this.
    Reply With QuoteRegister To Reply
  9. 11-03-2014, 03:58 PM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to convert or transpose list into table?

    Here's a commented version of the macro to help with your examination:
    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  10. 03-27-2016, 05:38 AM #10
    vakkarasu
    vakkarasu is offline
    Registered User
    Join Date
    03-27-2016
    Location
    INDIA
    MS-Off Ver
    OFFICE 10
    Posts
    5

    Re: How to convert or transpose list into table?

    Tnak you !
    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. convert this table into a list
    By gxatzis30 in forum Excel General
    Replies: 2
    Last Post: 06-04-2014, 08:22 AM
  2. Convert Table to List
    By Joanne Lim in forum Excel General
    Replies: 4
    Last Post: 05-12-2014, 02:36 AM
  3. Convert a table into a list
    By lj3287 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-16-2014, 03:46 PM
  4. [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
  5. Convert table to list
    By kvn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2012, 09:51 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