+ Reply to Thread
Results 1 to 14 of 14

Data consolidation from Multiple CSV files and then Split data into number of files

  1. #1
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Data consolidation from Multiple CSV files and then Split data into number of files

    Hi All,

    I am stuck with a unique problem and would really appreciate some help here.

    I am consolidating data from Multiple CSV files into one, i have used a VBA macro to do that, my problem is that once data is consolidated into single CSV files i want to transpose set of data from Rows to Column, and then insert plank column after every 5th row, apply true or false formula in row 5 to check that anything is mis match in row 2 and row 4, then based on mismatch values create seprate sheets so that they can be distributed.

    Please help, set of 4 rows with each row has 1681 column entries which should transpose and then apply required formula.
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Can you upload a sample output file showing us how you want the output?
    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]

  3. #3
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Hi Enclosed is ourput file, i have added 3 sheets to the current sheet. fist master sheet carries duplicate entries so first 2 rows are compared to 3rd and 4th row.

    output files are now identified by Question number 1306,1316
    Attached Files Attached Files

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button

  5. #5
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Hi Arlette,

    Thank you so much for this, this makes things easier, just wanted to check that is it possible to apply pass fail formulae to this sheet, i see that you have added formulate however the sheet is not reflecting the differences.

    Would request you to help with this, thank you so much

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    If you check this part of the code -
    Please Login or Register  to view this content.
    I have already incorporated the pass and fail formulae. Which part of it is not working?

  7. #7
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    the formula part is not working at all. the code u shared helps form multiple sheets however the formula is not working. so now i am selecting all sheets and making it manually

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Can you attach a sample file which you are running it on? I ran it on your earlier sample file and it works.

  9. #9
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    PFA file, i have added this
    Attached Files Attached Files

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    You have put the code in "ThisWorkbook". You need to put the code in a blank module.

  11. #11
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Apologies for going back and forth on this, Enclosed is template with changes, i have added them to a blank module, still it gives me same error, would it be possible to check this at your end and edit code if required.
    Attached Files Attached Files

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    There is some print character in row 49 which was being considered as the lastrow in your file and since it was turning up as blank, the macro was giving an error and not proceeding with the calculation part of the macro.

    Change this line in your code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    And it will work.

  13. #13
    Registered User
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    Thank you Arlette for helping me resolve this.

    Warm Regards,
    Neeraj

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Data consolidation from Multiple CSV files and then Split data into number of files

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    I will do it for you this time.
    Last edited by arlu1201; 09-18-2012 at 05:28 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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