+ Reply to Thread
Results 1 to 6 of 6

Converting multiple lines to one and merging workbooks

  1. #1
    Registered User
    Join Date
    09-17-2015
    Location
    Earth
    MS-Off Ver
    2016 Pro
    Posts
    2

    Converting multiple lines to one and merging workbooks

    So I've been tasked to combine 92 unusable workbooks into a single useful workbook. The attached workbook is an example of the 92 headaches I have. I'm not much of an excel guru, more of a script kitty (kid-die is censored? lol), but I know that if I hand-jam all the data it will take me forever.

    If you're looking at my attachment (attachment data has been sanitized to protect the actual data, sorry) you will see six "blocks" of data. What I need to do is pull the data from all 92 workbooks and put it into one workbook presented like columns N-T in the attachment. The source workbooks are always three blocks across, but don't always contain the same number of blocks. They are located in the same columns, but don't always occupy the same rows because each block doesn't always contain the same amount of data. The blocks always have the same heading; listing the worker, start location, info1, and info2. What changes is the number of white lines below each of the yellow "Time, job @ location" lines. The comma and @ sign always appear in the yellow line. The date column is derived from the file name of each workbook. The dates in the file name appear in alphabetic format ie June 1 = GR, June 2 = GS, June 3 = GT, June 10 = HA, and Aug 31 = KE... if that makes any sense... the file names all start with (X) followed directly by there alphabetic date AKA GR. So a workbook's file name might appear as (X) GR.xls

    Hopefully I've described the problem adequately. I don't even know where to start with this... please help.
    Attached Files Attached Files
    Last edited by GBorrelli; 09-17-2015 at 06:39 PM.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: I don't know how to describe my problem in one line... please help!

    Do the comma and at symbol ALWAYS appear in the text?

    In Column N, your header is Date, but you don't show a Date in the data. Is the date part of Time?

    I assume the worker name is in Blue?
    Last edited by Tinbendr; 09-17-2015 at 09:17 PM.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    09-17-2015
    Location
    Earth
    MS-Off Ver
    2016 Pro
    Posts
    2

    Re: I don't know how to describe my problem in one line... please help!

    The coloration of the cells is accurate to the actual workbooks. The cells are ALWAYS colored in that fashion and the comma and @ sign always appear in those locations. I have updated the example to better reflect the desired output. The date is actually derived from the file name of EACH workbook. The date is in an alphabetic format. Like June 1 = GR, June 2 = GS, June 3 = GT, June 10 = HA, and Aug 31 = KE... if that makes any sense... it's a mess.

    This project is such a headache... I'm very afraid that it's going to end up being a 180 hour hand-jam-a-thon... ugh.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: I don't know how to describe my problem in one line... please help!

    Welcome to the forum, GBorrelli.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Last edited by shg; 09-17-2015 at 07:39 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: Converting multiple lines to one and merging workbooks

    OK, do the Words "Start Location" exist as a header or is it the actual location name?

    Does an empty row/column actually exist between records?

    Are the records of data an Excel Table?

  6. #6
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,112

    Re: Converting multiple lines to one and merging workbooks

    I made code the seems to work on your example.
    2 things: in VBE,
    1.insert a CLASS then insert that code,
    2. insert a module and paste its code
    then run : BuildWork

    insert a new class ,SAVE IT AS: cWORK
    Please Login or Register  to view this content.
    this code goes into a module:
    Please Login or Register  to view this content.

+ 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. [SOLVED] Need Gradebook Help (unsure of how to describe problem)
    By calebjcook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2015, 09:40 PM
  2. Replies: 2
    Last Post: 11-18-2012, 11:20 AM
  3. Describe my problem? Where do I start....
    By Bulldog63 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-13-2012, 01:49 AM
  4. I'm not even sure how to describe this problem
    By EarlMachen in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-02-2008, 05:29 PM
  5. Replies: 0
    Last Post: 08-26-2007, 10:49 AM
  6. Can't describe....
    By ChrisMattock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2007, 11:58 AM
  7. [SOLVED] Macro problem on, Yellowed line - previous line or next line.
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-29-2005, 07:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1