• 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.

Consolidation from different files

K

KIM Chanthan

Member
Hi,

Do you have any good way to consolidate the data from different files into one master file?
Let say I have 2 files and 1 file contain 3 sheets. What I want is to consolidate each sheet in each file into one file.

Please view attached file for more detail.

Thanks very much for your creative idea and support.
C
 

Attachments

  • Result in Master File.xlsx
    9.5 KB · Views: 7
  • A-2017-July-01.xlsx
    9.4 KB · Views: 8
  • A-2017-July-02.xlsx
    9.3 KB · Views: 4
Some terms ...
is it this what are You looking?
Open it in any browser.
 

Attachments

  • moving.gif
    moving.gif
    579.1 KB · Views: 15
Dear Vletm,

Thanks for your time, but it is not like what I wish. My point is I want to consolidate data from many files into one file. Example: I have one file name AA, it contains 1 sheet and the data from row 1 to row 5. Also I have another file name BB, it contains 1 sheet and the data from row 1 to row 10. So I want to bring all data from this 2 files into another file called Master file. It must be contain 1 sheet and the data must be from row 1 to row 15.
Hope it explain.

Chanthan
 
vletm said:
KIM Chanthan
This is a bit of different wish than Your original
It would be possible to do with VBA
Or
use basic 'Copy&Paste' from file-to-file.
Click to expand...
Dear Vletm,

Thanks for your reply. I also thinking of copy and paste, but it might takes much time. Anyway, do you have any VBA code to do it?

Thanks,
Chanthan
 
Dear Vletm,

It's amazing tool. It works very well in the example sheet, but when i add more sheet to the real situation, it works only 1 sheet. Is there any problem if I add sheets and change the template? Attachment is what I have change.
Really appreciate your support.

Chanthan
 

Attachments

  • Result in Master File - Copy.xlsb
    28 KB · Views: 2
KIM Chanthan Let say I have 2 files and 1 file contain 3 sheets
I worked with three sheets.
There are only challenges, no pr... cases!
Many times there can be challenges if change 'an idea',
because it have to write to code just what it should do!
I modified code and now there could be 'any number of sheets'.
Of course, same name sheets have to be in both files!
Older version tested ( You can see yellows ).
Newer version ... cannot test, because no data!
 

Attachments

  • Result in Master File.xlsb
    22.6 KB · Views: 3
  • Result in Master File - Copy.xlsb
    29 KB · Views: 2
vletm said:
KIM Chanthan Let say I have 2 files and 1 file contain 3 sheets
I worked with three sheets.
There are only challenges, no pr... cases!
Many times there can be challenges if change 'an idea',
because it have to write to code just what it should do!
I modified code and now there could be 'any number of sheets'.
Of course, same name sheets have to be in both files!
Older version tested ( You can see yellows ).
Newer version ... cannot test, because no data!
Click to expand...
Dear Vletm,

The file is very helpful. I tested with few files with the data and it works perfectly. Thanks you very much for an amazing code :)

Chanthan
 
vletm said:
KIM Chanthan Let say I have 2 files and 1 file contain 3 sheets
I worked with three sheets.
There are only challenges, no pr... cases!
Many times there can be challenges if change 'an idea',
because it have to write to code just what it should do!
I modified code and now there could be 'any number of sheets'.
Of course, same name sheets have to be in both files!
Older version tested ( You can see yellows ).
Newer version ... cannot test, because no data!
Click to expand...
Dear Vletm,

As I just got another problem to consolidate many file into one and I am very new in VBA, can you help me to edit some code for the attachment? I have many file, but its header is the same. And I want to consolidate all those file into one called MasterFile. Anyway, each file has only one sheet.
Again, thanks for your strong support.

Chanthan
 

Attachments

  • File 1.xlsx
    8.5 KB · Views: 2
  • File 2.xlsx
    8.6 KB · Views: 1
Problem?
Problems sounds always bad!
Isn't that just challenge?
You sent to same kind of files ... and ... hmm ... after that something?
If just few lines in those two file ... isn't it quicker to do that manually?
 
vletm said:
Problem?
Problems sounds always bad!
Isn't that just challenge?
You sent to same kind of files ... and ... hmm ... after that something?
If just few lines in those two file ... isn't it quicker to do that manually?
Click to expand...
I know the problem is quite the same; only different header and sheets. It's because I am very basic on that. Anyway, if VBA can help, it might save alot of time instead of consolidating hundred files every month manually.
 
Cannot your merchants use same file ex two days
or the whole month
or even longer time?
Of course they can send daily same file for You,
but the last file matters!
 
vletm said:
Cannot your merchants use same file ex two days
or the whole month
or even longer time?
Of course they can send daily same file for You,
but the last file matters!
Click to expand...
Dear Vletm,
It's because i need them to send me those report daily because of our internal requirement and yes those files are for each day only.
what i want is to consolidate all those file everyday.

Thanks,
Chanthan
 
hii @KIM Chanthan ,

you can download Power Query from Microsoft site
Using power Query you achieve desire Result .



1.Go to Power Query Tab >>>From File >>From Folder>>>
2.Select Browse option >> Select Location >> ok
3.Screen will open
4.Right Click on Content Column >> Select "Remove Other Columns "
5.Double Click Down arrow.
6.Combine Files Window Files >>Select "XX" Sheet >>> ok >>
7.Right Click On "Name Column" >>> Uncheck "Null"
8.Click Close & Load Option




Regard
Rahul shewale
 

Attachments

  • Result in Master File.xlsx
    23.2 KB · Views: 2
  • consolidation-from-different-files.pdf
    371.2 KB · Views: 2
rahulshewale1 said:
hii @KIM Chanthan ,

you can download Power Query from Microsoft site
Using power Query you achieve desire Result .



1.Go to Power Query Tab >>>From File >>From Folder>>>
2.Select Browse option >> Select Location >> ok
3.Screen will open
4.Right Click on Content Column >> Select "Remove Other Columns "
5.Double Click Down arrow.
6.Combine Files Window Files >>Select "XX" Sheet >>> ok >>
7.Right Click On "Name Column" >>> Uncheck "Null"
8.Click Close & Load Option




Regard
Rahul shewale
Click to expand...
Dear Rahul,

Thanks for your help, but I don't have power quiry in my pc. One more thing, you answer to the wrong question. It's because of my mistake to put 2 different question into one stage. I will update new post on that.
Again, thanks for your hard work. :)
 
You must log in or register to reply here.
Back
Top