combine data from multiple files/sheets into single sheet and label with file/sheet name
I frequently have the same data collected for several different semesters, and I need to consolidate it into a single worksheet. I'm pretty sure this step alone I can do with the consolidate function? But what I cannot figure out is the following: I can't just merge all the data points into a single sheet as they are in their original sheets/files--I need a new column to be created in the new master sheet (that contains all the data points from the different files) that gives something simple like the filename (if in a different file) or sheetname (if in a different sheet) for each individual data point, so I can track to which semester it belongs.
For example, suppose each sheet/file has a student ID number, the name of a course that they took, their course grade, etc. Suppose I named each file/sheet by the semester--e.g. fall_2014.xls. Then in the master file, I would want each of those individual records to including the data that was in the original worksheet (with the column names taken from any one of the original files/sheets, which I could specify manually if needed), such as ID number, course name, grade, etc, but also a final column at the far right labeled semester with fall_2014 added for the data points that came from the fall_2014.xls file.
I know that I could manually insert the column into every single individual sheet/file, but that is time consuming, and since I often have to do this for different datasets, I would really like to find a more automated solution. I'm comfortable with complex formulas, but pretty hopeless with VBA code.
Does anyone have any suggestions about how I might be able to find an more automated approach to doing what I would like? Thanks for your time!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By ricdamiani in forum Excel Programming / VBA / Macros
Last Post: 08-07-2013, 12:07 AM
By Pradeep M B in forum Excel Programming / VBA / Macros
Last Post: 04-07-2013, 09:37 AM
By laurann in forum Excel Formulas & Functions
Last Post: 01-31-2013, 02:38 PM
By cndesu in forum Excel Formulas & Functions
Last Post: 12-28-2012, 08:25 PM
By bpatters69 in forum Excel General
Last Post: 10-19-2011, 08:33 AM
By mpquin in forum Excel Programming / VBA / Macros
Last Post: 03-12-2009, 06:06 PM
By yahata in forum Excel General
Last Post: 04-22-2007, 06:54 AM
By Newbie1092 in forum Excel Formulas & Functions
Last Post: 12-19-2005, 01:25 PM
Tags for this Thread
- 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