+ Reply to Thread
Results 1 to 12 of 12

Multiple file merge output incorrect.

  1. 05-09-2013, 03:51 PM #1
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Multiple file merge output incorrect.

    I posted this earlier "I am working on a project that will require me to extract data from over 300 workbooks, looking for a simplified solution for extracted the data and compiling onto one new file. All workbooks are located at C:\Documents and Settings\af91468\Desktop\Inspections and I need to extract data from cells B1 through H27 (large range due to merging) I actually need the data from these cells specifically though a range will also work (B1:B6 AND B22:B27) Also some of this data will be the result of a formula. I am attaching a sample of the workbook that I need to extract. Any help would be awesome" and i had thought i found the resolution but the output is not how i want it with the code below and help to change code to reflect the attachmentSample Export.xlsSample Import.xls

    Please Login or Register  to view this content.
    Last edited by arlu1201; 05-10-2013 at 02:49 AM. Reason: Use code tags in future.
    Reply With QuoteRegister To Reply
  2. 05-10-2013, 05:51 AM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Multiple file merge output incorrect.

    Firstly, please do not create duplicate threads. If you do not receive a reply, you can bump your thread.

    Coming to your question, do you want all the data to be stored one below the other in column B of the summary file? Do you want the file name to also show?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 05-10-2013, 10:40 AM #3
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Re: Multiple file merge output incorrect.

    Appoligies, thought I had figured it out but was unsucessful. No need for file name. What i would like to see is the results by row. Like this:
    Name Number Region Divison Area Start Date PL TE FC IM Comp Overall
    Sample 1 1 1 1 1 1 1 1 1 1
    sample 2 2 2 2 2 2 2 2 2 2 2
    Reply With QuoteRegister To Reply
  4. 05-14-2013, 02:06 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Multiple file merge output incorrect.

    You want the data to be consolidated starting in column A or column B of your master file?
    Reply With QuoteRegister To Reply
  5. 05-16-2013, 09:32 AM #5
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Re: Multiple file merge output incorrect.

    Quote Originally Posted by arlu1201 View Post
    You want the data to be consolidated starting in column A or column B of your master file?
    Looking to start in Column A
    Reply With QuoteRegister To Reply
  6. 05-16-2013, 10:44 AM #6
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple file merge output incorrect.

    I have tested this code on PC and works. The reason why you are having an issue is may be due to merge cells. You need to unmerge them.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-16-2013, 12:03 PM #7
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Re: Multiple file merge output incorrect.

    Works perfect... Another question for you is with this same code can i add additional cells that are not in a range such as b1:b27 and f43 and l46 etc... Also is there a code to unmerge the cells in all of the files?

    Thank you again, you have been a lifesaver...
    Last edited by adamcfishman; 05-16-2013 at 12:06 PM.
    Reply With QuoteRegister To Reply
  8. 05-16-2013, 12:20 PM #8
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple file merge output incorrect.

    Yes, you can, need to adjust, this line.
    Please Login or Register  to view this content.
    There are many ways of doing it. You can loop through certain columns(Although it may slow down the speed)
    If you have a fixed range to copy, you can use for example Union
    Please Login or Register  to view this content.
    It all depends on the format of your data.
    To unmerge cells

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 05-16-2013, 12:24 PM #9
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Re: Multiple file merge output incorrect.

    Where would i add the code for unmergeing?
    Reply With QuoteRegister To Reply
  10. 05-16-2013, 12:28 PM #10
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple file merge output incorrect.

    Try to change the line

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 05-16-2013, 12:38 PM #11
    adamcfishman
    adamcfishman is offline
    Registered User
    Join Date
    02-23-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    13

    Re: Multiple file merge output incorrect.

    I modified this:
    Please Login or Register  to view this content.

    It does not seem to be unmerging files as results for only b1:b6 are displayed and all results are in column A
    File 1
    b1
    b2
    b3
    etc.
    file 2
    b1
    b2
    b3

    should be

    File 1 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
    File 2 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
    File 3 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
    etc through file 426
    Last edited by arlu1201; 05-18-2013 at 03:05 AM.
    Reply With QuoteRegister To Reply
  12. 05-16-2013, 03:47 PM #12
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple file merge output incorrect.

    adam,
    Please use code tags with your code.
    If there is nothing to unmerge, this will not affect running the code.
    The reason for including this line is in case you have merged cells, the code may give you an error, so the code unmerges the cells before it is looping through the range, but if there are not merge cells, the code works as intended.

    As to this line

    Please Login or Register  to view this content.
    Ohm!!
    I gave you just an example. It will not work until you get the lower and upper boundary right as the code, which was written by Ron Deburin, uses an array to copy back the range.
    I suggest you to use similar code, which does not have array. A code which gives you flexibility and you can also easily amend it yourself.
    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)

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