Merging several Excel spreadsheets together
Discussion
Any tips for merging spreadsheet data together.
I have several with customers details to be used for mailshots etc, I need to remove duplicates and make sure one person is on one row only. titles such as busines name, email and so on are all in the right places using the newest data as priority.
I am sure there is probably some simple techniques but I am not an Excel guru and google so far has resulted in too much guff.
Probably around 3000 people, and trying to avoid doing manually!
Many thanks.
I have several with customers details to be used for mailshots etc, I need to remove duplicates and make sure one person is on one row only. titles such as busines name, email and so on are all in the right places using the newest data as priority.
I am sure there is probably some simple techniques but I am not an Excel guru and google so far has resulted in too much guff.
Probably around 3000 people, and trying to avoid doing manually!
Many thanks.
I don't know if there's an easy way to do it with spreadsheets themselves, but this would work (assuming that each spreadsheet has the date in the same columns)...
1. export the spreadhsheets to .csv files
2. collect all the .csv files into a folder
3. create a new .bat file in that folder with the line
5. run the .bat file
4. load merged.csv back into excel
5. sort the data so you can easily identify duplicates
If you get stuck you can PM me the sheets and I'll merge them
1. export the spreadhsheets to .csv files
2. collect all the .csv files into a folder
3. create a new .bat file in that folder with the line
copy *.csv merged.csv
5. run the .bat file
4. load merged.csv back into excel
5. sort the data so you can easily identify duplicates
If you get stuck you can PM me the sheets and I'll merge them
boony said:
I don't know if there's an easy way to do it with spreadsheets themselves, but this would work (assuming that each spreadsheet has the date in the same columns)...
1. export the spreadhsheets to .csv files
2. collect all the .csv files into a folder
3. create a new .bat file in that folder with the line
5. run the .bat file
4. load merged.csv back into excel
5. sort the data so you can easily identify duplicates
If you get stuck you can PM me the sheets and I'll merge them
I've used this method before, works well, especially as we were working with CSVs anyway.1. export the spreadhsheets to .csv files
2. collect all the .csv files into a folder
3. create a new .bat file in that folder with the line
copy *.csv merged.csv
5. run the .bat file
4. load merged.csv back into excel
5. sort the data so you can easily identify duplicates
If you get stuck you can PM me the sheets and I'll merge them
How can/are you identifying which is the most current data for each person? If you're not careful you could end up picking the wrong record & it's possible you will have to manually check when there is more than one entry. When you have merged all the tabs onto a single list you could sort by something eg customer last name, assuming it's been entered consistently. This will put all the Smiths together & then you may have to flag each entry to be deleted. If you change the cell colour you can filter by this & do all the deletions in one go.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff