1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel : intelligently cleaning and transforming a table

Discussion in 'Business Applications' started by Ataraxiste, Feb 20, 2007.

Thread Status:
Not open for further replies.
  1. Ataraxiste

    Ataraxiste Thread Starter

    Joined:
    Jul 22, 2004
    Messages:
    22
    Hello,

    I am currently trying to clean a file containing a collection of tables. Basically, I want to keep only selected parts of those tables, and clean up the mess.

    For the moment, the excel file looks like this : (see attached sample file)
    - The file is a collection of independent tables
    - The independent tables are torn apart multiple isolated tables. Each independent table is preceded by a cell starting by "segment [name of the segment]". Sometimes, this identifier is separated between different cells, but it is always on one line, and it is the only thing on this line.
    - inside each independent table, there are many unwanted items, and the number of rows is not constant for each item, and sometimes an item may be separated between two isolated tables.

    I would like it to look like this : (see attached transformed sample table)
    Basically, regroup the mutltiple isolated tables into one table, keep the identifier of this independent table (keep the row corresponding to "segment [name of the segment]"), and only keep the rows corresponding to one particular operation in column B ("Séchage (02)").

    I have started to do it by hand, but I think it should be possible to do it much faster with some kind of macro. Would any of you be able to help me ?
    Thanks a lot !
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Try this, the data is so unstructered that it might not work for all formats, but does for the ones that you supplied.
    At most you may lose a Table Heading or 2.
    You may have to adjust the for/next loop count to accomodate all of your data as you have not indicated how much there is.
     

    Attached Files:

  3. Ataraxiste

    Ataraxiste Thread Starter

    Joined:
    Jul 22, 2004
    Messages:
    22
    Thanks OBP. I will indeed have to adapt it for the more complex parts, but at least it gives me a squeleton to work inside.
    Cheers !
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/545625

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice