-1

I have a client who sends out a physical newsletter to tens of thousands of customers. They have one list of physical addresses, but every year they receive a new list from the company who runs their box office and they need to merge this list into the main list, removing duplicates. This list doesn't contain e-mail addresses, and the only column which could reliably be used to filter out duplicates is the post code.

I need something which will merge the two spreadsheets, and when it finds a duplicate in the postcode field it will delete the whole row, leaving just one row of address details with that postcode. I'm happy to recommend any software or service to my client, but I can't seem to find anyone using spreadsheet software who has had this same problem. To clarify, I'm not looking to add extra columns from one spreadsheet to another, but to take two spreadsheets full of customer address details and present the client with one spreadsheet with all of those customer details on, but with duplicates removes, so that they can send that to their printer.

Any help would be greatly appreciated!

Tristan McDonald
  • 141
  • 1
  • 9
  • possible duplicate of [Join two spreadsheets on a common column in Excel or OpenOffice](http://stackoverflow.com/questions/4160243/join-two-spreadsheets-on-a-common-column-in-excel-or-openoffice) – tohuwawohu Jul 03 '15 at 07:32
  • Thanks, but I'm not looking to add extra columns to rows, just to remove entire rows if they have duplicates in the post code column. – Tristan McDonald Jul 17 '15 at 04:57

1 Answers1

1

Well, it seems quite easy with Excel only.

  1. Place the 2 data sources in the same excel table
  2. Insert a new column in this table
  3. Enter this formula in the new column :=COUNTIF([PostCode];[@PostCode]) It counts the number of time the postcode of the row in present in the column.
  4. Filter the table (remove the "1")
  5. Deleted the visible rows
Musisold
  • 131
  • 7
  • So will this remove the whole row if the postcode has already appeared earlier in the document? – Tristan McDonald Jul 17 '15 at 04:53
  • That seems helpful, but if it only produces an extra column telling me which rows to delete I will note be able to do that manually (there will be over 120,000 rows in the combined spreadsheet). – Tristan McDonald Jul 17 '15 at 05:02
  • if you filter the table on the rows that need to be deleted, you can easily delete them all... (select all, right click, delete) – Musisold Oct 15 '15 at 10:48