Hello, My data is divided in 3 workbooks i.e. Data1.xls , Data2.xls & Data3.xls saved in folder "D:\SalesTrendReport" .
I needed to get Sales qty data from these 3 files to my workbook 'Sales Trend.xlsx' based on Office Code & Product Code.
Sales Trend.xlsx
Sales Trend.JPG
Data1.xls
Data1.JPG
Data2.xls
Data2.JPG
Data3.xls
Data3.JPG
Attached my 3 data files.
Why not all data in 1 worksheet and after that analyse the data?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Sir, These were just for example. My actual data is divided in over 15 workbooks with many other set of office codes. So combining all in a single worksheet is a hectic task. Is it possible to get this with formulas/vba to work with existing divided data files?
Is this a task you have to do often for this (same) files, or just 1 time.
If you have to do this ofter with the same time, I would go for 1 workbook, with all data in 1 worksheet. After that I would analyse the data.
If it was just one time, I would open the files. Rightclick in the tab-box and choose (click) for Make a copy. Then in To file, click on new file => a copy of the data are stored in a new workbook. Repeat this action for the other files.
Last edited by oeldere; 07-05-2020 at 09:45 AM. Reason: text changed
Sir, These data files I download now & then from sap bi portal from which I can export/view upto max 6 months data only. If I have to compile data from last 5 years or so, it will generate many files. So combining them in a single workbook 1 by 1 take much of time.
I am not able to open the xls files at the moment. Since you use excel 2010 you could have post the xlsx files.
You probably don't get my point. Since you can only download 6 months, it would be easier if you add those new information (download) in a file which already contains the values of the earlier 5 years (minus the new 6 months).
If you do it your way, you always have to collect 9 files (4,5 year).
Those values are already available in your earlier collection.
Attached data files in .xlsx format.
ps. My default exported data files are in .xls format only.
Did you understand my comment?
You did not respond on that.
Maybe you will get trouble with the merged cells in A1 and B1.
My advice would be don't use merged cells.
Sir, I didn't have previously saved compiled data of past years in a single file.
Sir, merged cells in data files are coming in every exported files by default.
Which version of Excel are you using?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
How you add those information to the excel file, since it is not available?
Please Login or Register to view this content.
If you use a formula for your request, the merged cells will cause problems (sooner or later).Please Login or Register to view this content.
You can use the file, with the directory in cell A1.
You can run the code below, which is in this file.
Please Login or Register to view this content.
Last edited by oeldere; 07-05-2020 at 10:38 AM. Reason: text changed
Ma'am I use Excel version 2010.Originally Posted by AliGW
Sir, The code created new file which I'm attaching.
Yes, that is the idea, and after that you can analyse the data.
Sir, Now it requires to get the data from 'Sheet2.xlsx' which I've uploaded to my workbook 'Sales Trend.xlsx'.
There is no sheet2 in the files you added to the question.
edit
The filename is sheet2 (which is confusing).
But what needs to be done with that file named sheet2.
It is not clear (at least not clear to me).
Last edited by oeldere; 07-05-2020 at 12:37 PM.
Sir I was telling about this file which created after running the code. I've saved this file in the same folder by name Sheet2.xlsx
See the question in #19
But what needs to be done with that file named sheet2.
Sir, I'm sorry for not able to express properly.
I'm asking about how I can add that data in my Sales Trend.xlsx file.
You question is (was) how to add the 3 (or more files) in 1 sheet.
You get code from me, how you can achieve that.
You did not mentioned earlier Sales Trend.xlsx fileI'm asking about how I can add that data in my Sales Trend.xlsx file.
Neither you did not posted this file (without confidential information).
How should I be expected to know what needs to be done with that data / file if you can't explain it.
.
Sir, I asked for the same at my thread opening post already.
The file does not contain any confidential info. The file has dummy figures and codes.
In my earlier explanation I missed 1 step.
first use this code in each file with data.
Before you run this code make sure:
1) Change the name of the sheet in Data
2) Copy the data in row 2 (the green row)
The result of the code is in sheet output.
After you have done that with all files.Please Login or Register to view this content.
Use the code I gave you earlier (also in this file).
After that a pivot table.
See the attached file.
A) 1files-data1
B) 2020-07-05 (oeldere) abhinavbinkar mergefile
The endresult is in file 2020-07-05 (oeldere) abhinavbinkar mergefile sheet PT Oeldere.
Sir,
First of all I'm sorry that I haven't replied coz I was unwell. I tried the code today, and some things are uncleared.
Do I need to change all my Data workbooks sheet name to 'Data' and then run the code on each workbook one by one?
Not understand this step completely.
As I dont know abou pivot tables, can the vba code help to automate the above steps.
Please help.
1) That is why you need to change the name of the file in data. The code takes the data from sheet DATA.Please Login or Register to view this content.
HTML Code:2) You need to have the data (the green row) in row 2. (this is the row for the headers).HTML Code:
3) After that you can create a pivot table to analyse the data like I did.
Last edited by oeldere; 07-26-2020 at 04:38 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks