Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Mathijs Gast
10,439 PointsComparing and then deleting records from database
Hi guys,
I'm currently working on a database project for a client and I am having problems how to solve the following issue. I have one excel sheet with over 10.000 records with columns like name, address etc. and I have another excel sheet with 1.300 records with exactly the same data (but less records obviously). What I would like to do now is to delete those 1.300 records from the other excel sheet.
What is the best and easiest way to do this? Import both sheets in Access and then write a SQL code to make this happen or are there other solutions?
Thank you very much in advance.
Mathijs
4 Answers
Gareth Borcherds
9,372 PointsDo the two files have a unique identifier to identify the records?
If so, vlookup in excel will do the trick. Just go to a blank column in your sheet of 10k and then write a vlookup using the unique id, then lookup that id in the other spreadsheet. If it returns a result, then you can delete it, if it doesn't, then don't delete it. You can then use a simply filter in excel to filter for returned values and then delete all of them.
If you don't have a unique identifier, you can create one via a concatenated string.
Mathijs Gast
10,439 PointsThanks for responding quickly. That might be a working solution, to be more specific I don't have a real unique identifier, instead I have a column with "Company Name" and a column with "Address". Some of the company names might be the same, but have different addresses, so I don't want to delete those as well. I knew the vlookup method, but never heard of the concatenated string.
Could you help me further with this? I really appreciated it ;-)
Gareth Borcherds
9,372 Pointsyeah, concatenating a string in excel is as simple as =a1&a2 this will concatenate cells a1 and a2. So if you have two columns and you concatenate the strings, then you have a unique record for company name and address. You can then do the same concatenation in the other file and where they match you can delete with the vlookup function.
Mathijs Gast
10,439 PointsAha yea I understand ;-) Thank you very much sir for this effective and easy solution!!! Really helped me out.
Cheers,
Mathijs
Gareth Borcherds
9,372 PointsA best answer selection would help :)