combine excel to single masterfile

[dhea]

i have problem to combine several excel file (up to 8000) to a single master file.there are several file in excel that have particular such as name, age,gender..etc in a horizontal.


Name: Sllay

Age: 23

Gender: Female


so i need to transfer the data to a new worksheet and change the data to a vertical line so that can be sort.


Name Age Gender


anyone have suggestions how to do this without need to copy n paste every single sheet

 

[Luke M]

Check out the copy/paste/merge examples on Ron's page:

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


He's got several good macros for creating summary workbooks.

 

[madmarki]

Ron de Bruin also does a really neat Excel Add-in that utilises his various merging techniques:


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


it is very versatile, and pretty fast.

 

[dhea]

i cant run the RDB merge after download..how?

 

[srinidhi]

its an Add in click on the excel office button > excel Options >Add Ins, from the manage drop down box select Add ins & click go the add in box will open up, browse to get the add in & click ok

 

[Jack]

Hi Dhea


Firstly I agree with others Ron de Bruin's site is brilliant for this kind of merging and data manipulation - you need to know enough VBA to amend his paths set. So it really depends if you want a VBA solution or no VBA solution. I use his code for merging .csv files for data dumped out of core system and it works brilliantly. RDB merge I am aware of and I think if should perservere on this as this will be the fastest path.


But if this fails and assuming its a non VBA solution you are after... you need to go down the Advanced FOrmula route. I have no idea if you are strong with formulas so hope what follows isn't gobly gook or at the other end of the scale boring but my approach would be ...


I would think there would be one key field common to all the spreadsheets (might be a unique name that is a combination of surname and a numeric??) which can be used to match with the different info in other sheets and then once matched reference the right column from the target back to the destination. If there isn't you can create one (I had to do exactly this to match a similar size customer database) so I created concatenated First name & second name & DOB into a nonsence value that was pretty much unique as my key field. I used TRIM/CLEAN in the concatenate formulas to minimise spaces and unseen charaters mucking up the unique value you are creating.


So then you could create one master sheet (the destination) and copy this one key field of 8000 lines into say col B - you will use this as the test against other sheets. Then in another sheet called say Control you make a table of all references showing sheet names, field headings, column numbers in the target sheets for the data you need - you can use formulas to do this easily CELLINFO, COLUMN() and just + cell address.


Back to the master sheet you create a bunch of Index/ match combination formulas in columns left to right after B that use the right sheet reference and column numbers using your key field in the master sheet to get a match in the other sheets and then this will being in the other info into you master sheet. If you are strong with advanced formulas (and you probably don't need my lengthy essay) you could use ADDRESS and INDIRECT formulas on the references in control from the master sheet. If you are not familiar with these types of formulas then admittedly there's a steep learning curve ahead but Index/Match como formulas (Chandoo has a good recent post on this I recall) its a really worthwhile challenge to get into because if you can do this you will find you can get really confident on any data manipulation and consolidation using formulas.


My thoughts in the 5 mins I have thought about what approach I would take. Maybe you only need 10 columns of data from the other sheets so this would be doable exercise. I know I am glossing over a whole host of detail but I am just trying to describle the approach I actually took to do a similar thing but I didn't have several worksheets just 2 but I did have 8000 rows and basically merged selected data from the target back to the source. So your task needs more careful referencing than my task because there are more sheets.


If you know VBA then I think Ron de Bruin's site is the way to go.


Hope this is of value and not the most boring post on this forum yet.