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

Output data from Multiple sheets into one, and verify nothing is duplicated!

W

WarriorAdmin

New Member
After several days of searching, I must ask the experts!


My task is to verify equipment being used is not duplicated.


I use 10 workbooks (seperate files named for a foreman)

Each with worksheets named for each day (Monday, Tuesday etc).

Each day has a list of the equipment (by 3-4 digit number {456, 365, 4768 etc.) used in the cell range CU10-CU20.


I need to take the data from each day, output to a single sheet, and verify that no one else used the same equipment the same day. I’m imagining a sheet with days of the week in columns, and each foreman in rows.

It’s okay if it will take a few steps, I’ll be the person running the repot. VBA is also okay.


I found a formula for merging data from the first sheet of each workbook in a folder, which is a great start (http://msdn.microsoft.com/en-us/library/cc837974.aspx ) It outputs the correct data, but only for Monday (the first sheet.)


I cannot determine how to get Excel to:

1. Farm the data for all 7 days output into one

2. take that data, and sort it into a matrix of each foreman and each day the equipment number used (Also, the data from the above method outputs sort of scattered. (if I have 5 workbooks in a folder, each days data is output in a 10x10 grid, I expect it needs to be in one column to keep things neat.)

Please Help!
 
Sounds like the first trick is getting all the data into one place. Ron's got several articles on doing that here:

http://www.rondebruin.nl/tips.htm

(check out the section on Copy/Paste/Merge)

One of those macros should work for you, and you should (if it's not already included) be able to identify each sheet/workbook that the data came from.


Once you've got all the data together, it should then be pretty easy to do a comparison on each day to see if equipment is being over-booked.
 
I am definitely a newbie when it comes to VB.


He lists code to summarize a range in all of the sheets in a workbook onto one Summary sheet, which gives me each piece of equipment (range A10-A20 for example) for each day, listed in two columns (A=day B=equipment. So my summary page has A1-a10 = 10 spots for monday, then A11-A20, 10 spots for tuesday etc. The problem is the code stacks the data in one column. (below each other) http://www.rondebruin.nl/copy2.htm "Merge cells from all or some worksheets into one Master sheet"


If it could output the 10 cells for each day (each sheet) into it's own column on a summary page; this would be fine. If they were side by side like a calendar, I could merge the summary for each foreman onto one master sheet (each foreman below the next, with each day running in columns B-H like a calendar. I can't get it to output each sheet onto it's own column.


If i can do that, he presents the code to "Merge data from all workbooks in a folder (1)" http://www.rondebruin.nl/copy3.htm But this displays a new blank worksheet, with no data. The macro show titled "Example_1 or Example_3". I changed my filepath and the range of the data to the first sheet (summary) in case I get the summary to output like I need, but this won't work!


Tweaking the code Ron has prepared like this is well beyond my capability. I appreciate any help!


https://docs.google.com/open?id=0B0Ql6nWiLdDINjM0ZTg1ZmEtYzVjMC00MWI0LTk4MjctNWYwZWNjZWM2NTM2 is a link to a example workbook with a summary and Final summary page looking like I need, but without any code to make it happen. Please advise if I can explain anything better!
 
This will be a weekly responsibility, so I want to automate as much as I can. If I can output each foremans data into a summary of each day as stated, I can copy the data onto a master summary if need be. But, the more I can automate the better.


I think that I could make a summary page, denote monday - friday and simply make a table with references to the cells for each day (a2-a10) but I feel like that has potential for error across 10 files.
 
Progress!! Luke, i followed one of your other posts in the forums, and found that to pull data from the first sheet in each file in a folder, the excel sheet with the code in it, cannot be in the same folder as the files I am pulling the data from!


So, if I can figure out code to create a new summary page at the beginning of a workbook, vertically listing the range for each day(columns B-H), I have Ron's code (mergehorizontal) to create my master List and make the comparisons!
 
Update: I cannot find a formula or code to pull data from CU10:CU20 and output it into columns on a seperate sheet. (each worksheet is a day of the week, the ouput would look like a calendar listing the equipment below.


Does anyone even have ideas?
 
You must log in or register to reply here.
Back
Top