In retrospect, this is an issue of data-collection, but I want to see if there is an easy way to do it without doing each file.
I have about 350 separate Excel workbooks. Each workbook has a bit of information pertaining to one individual.
What I want to do is create a simply spreadsheet of everyone with the necessary data, so it is then usable.
To give you an idea. A1 lists "Name" with B1 having the person's name.
A2 has "Last 4 SSN" and B2 has the 4 digits.
Then A3 has the word "Year", then A4 "2014 est", A5 – "2013", A6 – "2012, etc, to 2009.
Then in Row 3 from B to N there are different pieces of information.
What I want to do, if possible, is have a master spreadsheet with columns of name, last 4 ssn, and then years for each of the pieces of data. ie. 2013-Interest
Is this even possible?I hope this makes sense.
Best Answer
I would use the Power Query Add-In for this. You can start a Query from an Excel table. If you spreadsheets are in separate files it has a great function to import all the files in a Windows Folder in a single step and append all their data together (assuming the file columns are consistent). If they are multiple sheets in a single file then you will need multiple Queries, which you can combine together using the Append command.
http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872
There is also an Unpivot command to transform data stored in multiple columns into multiple rows - maybe that would solve your multi-Year challenge.