• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

combining data in the 1st column from differen names excels into once consolidated file, maintaining file name as reference.

Hi

Aim to combine the data in the 1st column of each each excel spreadsheet into once consolidated file.

Please help to create me the VB that can do this. I searched and I was unable to do this.

1 .Each excel has a different name.
2. The data for each excel ending in trans is in column A and the number of rows can differ. [ATrans.CSV]
3.The data for each excel endng in White is in column A and column B and the number of rows can differ. AWhite.CSV
4. The tab name is the same as the file name.

5. Can we combine the data so I have one consolidated files ending in Trans. Column A would be the filename or tab name and column B the data.
6. Can we combine the data so I have one consolidated files ending with White. Column A would be the filename or tab name and column B and C the data.

All my files are in a folder call ARC

Eg. files name
AWhite.CSV
ATrans.CSV
BWhite.CSV
BTrans.CSV

due to limit i could not atached the file - ATrans.CSV
but it looks like this.
cell A1 1234 and tab is called ATrans

Exampleoutput

Combined_data_trans.csv
Combined_data_white.csv

Thanks

This would be a really blessing if someone can provide me with the code to run in the combined files.

David.
 

Attachments

  • Combined_data_trans.csv
    63 bytes · Views: 3
  • Combined_data_white.csv
    81 bytes · Views: 4
  • BWhite.csv
    21 bytes · Views: 3
  • BTrans.csv
    15 bytes · Views: 3
  • AWhite.csv
    18 bytes · Views: 3
Hi,​
as this is not 'Excel' - as Excel is an application ! - but only text files so why do you need Excel for this rather than any more efficient way ?​
Are only two files consolidate criterions ?​
 
As a .csv file is not an Excel file but obviously a text file !​
The reason why your 'consolidation' can be also achieved without Excel, to see with your IT.​
So if you want to start from Excel workbooks why did you not attach at least them rather than such text files ? So weird …​
And well read my previous post to revert the expected as guessing can't be coding !​
 
The attached works on raw csv files and produces raw csv files. No csv file is opened in Excel.

Clicking the button will
  • ask for where the folder is that contains your source files
  • create (if not already present) a new folder for the result files directly under the same folder as the source files
And:
  • the name of the new results folder is determined by the first line of the macro
  • running the macro more than once will overwrite any previous result files
  • the macro is wordy and can be cosiderably shortened, but as it stands it's easy to see what's going on so it can be tweaked easily
 

Attachments

  • Chandoo51135.xlsm
    42.1 KB · Views: 5
Last edited:
Yes, it can be achieved with an easier shorter code using only VBA basics - so without FSO - and an unique folder from a path variable …​
 
It's so nice when you've clearly put significant effort into helping someone and they then can't be arsed to acknowledge it; it really encourages you to help them again.
 
So true ! The reason why I wait first at least for answer to my question even if I already have a solution.​
The same if another helper posted some valuable question, I won't post my ready solution …​
And after giving a solution if I do not receive any revert, if it's not the first time,​
direct black listed for his future threads, won't waste time anymore for such people !​
 
The attached works on raw csv files and produces raw csv files. No csv file is opened in Excel.

Clicking the button will
  • ask for where the folder is that contains your source files
  • create (if not already present) a new folder for the result files directly under the same folder as the source files
And:
  • the name of the new results folder is determined by the first line of the macro
  • running the macro more than once will overwrite any previous result files
  • the macro is wordy and can be cosiderably shortened, but as it stands it's easy to see what's going on so it can be tweaked easily

sorry, I was unless -thanks for the asnswer, and the time you spend.
 
So true ! The reason why I wait first at least for answer to my question even if I already have a solution.​
The same if another helper posted some valuable question, I won't post my ready solution …​
And after giving a solution if I do not receive any revert, if it's not the first time,​
direct black listed for his future threads, won't waste time anymore for such people !​
i was not feeling well - so sorry for the delay and thanks for everthing.
 
The attached works on raw csv files and produces raw csv files. No csv file is opened in Excel.

Clicking the button will
  • ask for where the folder is that contains your source files
  • create (if not already present) a new folder for the result files directly under the same folder as the source files
And:
  • the name of the new results folder is determined by the first line of the macro
  • running the macro more than once will overwrite any previous result files
  • the macro is wordy and can be cosiderably shortened, but as it stands it's easy to see what's going on so it can be tweaked easily
just ran it - amazing
 
Back
Top