+ Reply to Thread
Results 1 to 12 of 12

Convert Multiple Tables on a sheet to a List

  1. #1
    Registered User
    Join Date
    08-17-2011
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    7

    Smile Convert Multiple Tables on a sheet to a List

    Hi everyone

    Finally after trying hard for the whole day, i am finally posting my problem.


    I have a sheet in which there are multiple tables ( can be even 400 tables in a sheet). Each table has a different column heading. Now i want to compile whole of the data into List format so that i can use Pivot tables & other tools to do advance analysis of the Data.


    I am attaching herewith a Sample sheet in which there are 2 sheets. One is the Input sheet & the other is output sheet, the format in which i need the output.

    I would appreciate if someone can help me with some macro which can collect data from multiple tables in a sheet & make a list of the same. It would be great if the macro works even if the size of the tables differs from each other.


    Please help as i need to convert about 1000 tables ( multiple files ) to list & do analysis & submit the same in 2 days
    Attached Files Attached Files
    Last edited by smartk; 07-22-2012 at 11:24 AM.

  2. #2
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Convert Multiple Tables on a sheet to a List

    Hi,

    See if this works. Put the data in Sheet "Input" and your result will be in sheet "Output"

    This code might not be the fastest you have ever seen I am a newbie as well.

    here is the code in the main module

    Please Login or Register  to view this content.
    code for the tools module
    Please Login or Register  to view this content.
    And the attachment

    SSS Format Sample.xlsm

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert Multiple Tables on a sheet to a List

    Try
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-17-2011
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Convert Multiple Tables on a sheet to a List

    Thanks for your effort, fredlo2010. When i use cleanup macro, it removes the blank rows along with the column headers for the 2nd table & beyond. Hence the macro treats all the data on the sheet having same row columns heading which is not so in my sample.

    Check this Highlight SSS Format Sample.xlsx
    Last edited by Cutter; 07-22-2012 at 08:54 AM. Reason: Removed whole post quote

  5. #5
    Registered User
    Join Date
    08-17-2011
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Convert Multiple Tables on a sheet to a List

    Thanks for the reply, jindon. But just like fredlo2010, you have not differentiated between the table column headings. My sheet has multiple tables & all the tables have different Column Headings . Check this Highlight SSS Format Sample.xlsx
    Last edited by Cutter; 07-22-2012 at 08:54 AM. Reason: Removed whole post quote

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert Multiple Tables on a sheet to a List

    OK, change to
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Convert Multiple Tables on a sheet to a List

    Hi jindon,

    I have seen your codes in this forum before, you have even helped me with stuff before and you have a way of coding that simplifies it a lot. But the problem is that you use things I have never seen or understand. Do you mind explaining a little bit so I can grasp the concept or at least guide me.

    Thanks a lot[COLOR="Silver"]
    Last edited by fredlo2010; 07-22-2012 at 08:52 AM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert Multiple Tables on a sheet to a List

    Is this easier to understand?
    Please Login or Register  to view this content.
    Difference between these two codes is using arrays or not.
    The algorithm is very simple.

    1) Loop through col.A that is not blank
    2) If "Month"(header) found, remember that row in "temp"
    3) else, loop through col.6 to the end of the column
    4) output each corresponding data.

    Previous code is just using Arrays to speed up the process.
    Any question?

  9. #9
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Convert Multiple Tables on a sheet to a List

    Thanks a lot jindon,

    This is awesome; the difference between the Array an the loop option is that I can actually step into the code and see whats doing.

    There only thing I would like to know in order to understand the code. What does each variable represent. I am sorry it is asking too much. I am trying to learn as much VBA as I can before I start my classes. Also smartk mentioned that this is just a sample, his real workbook columns to up "AL" I think he said. This will help him understand a modify the code as well.

    Regarding Arrays, I am not even going to ask. That's a concept that for some reason I find extremely hard to grasp.I wonder if I am the only one?

    Thanks a lot. This has already helped me a lot I will just have to analyze it in more depth.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert Multiple Tables on a sheet to a List

    If you step through the code with Local Window open, you can see each variables in the window and what it is doing.
    (while you are in VBE, goto [View] - [LocalWindow])

    If you asking the meaning of the "variable" itself?

    No, meaning, I use i, ii, iii etc as loop counter, a,b for Arrays, n, t for counter, r, c for Range, ws for worksheet, wb for workbook.

  11. #11
    Registered User
    Join Date
    08-17-2011
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Convert Multiple Tables on a sheet to a List

    Thanks a Ton jindon. It works flawlessly. You have made my job so much simpler. I have no programming knowledge, basically i have finance background but after seeing the speed at which this code speeds up my work, i am motivated to learn VBA coding. Can you guide me to some resource from where i can learn VBA coding from scratch ??


    Thanks!!!!


    Quote Originally Posted by jindon View Post
    OK, change to
    Please Login or Register  to view this content.


    ---------- Post added at 08:57 PM ---------- Previous post was at 08:53 PM ----------

    Thanks fredlo2010 for all your help.

    Quote Originally Posted by fredlo2010 View Post
    Thanks a lot jindon,

    This is awesome; the difference between the Array an the loop option is that I can actually step into the code and see whats doing.

    There only thing I would like to know in order to understand the code. What does each variable represent. I am sorry it is asking too much. I am trying to learn as much VBA as I can before I start my classes. Also smartk mentioned that this is just a sample, his real workbook columns to up "AL" I think he said. This will help him understand a modify the code as well.

    Regarding Arrays, I am not even going to ask. That's a concept that for some reason I find extremely hard to grasp.I wonder if I am the only one?

    Thanks a lot. This has already helped me a lot I will just have to analyze it in more depth.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert Multiple Tables on a sheet to a List

    You are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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