Open Office Spreadsheet help from you gurus

Hardware and technical stuff
1

Alright so our office is sending letters to about 1400 patients informing them of our change of address coming up as the doctor is joining a new group.

So I have a spreadsheet, using Open Office, and I want to remove any entries that are a duplicate address. This way we don’t send two letters to one household for a husband/wife patient. Is there any way to automate this process instead of just sorting by address and going through manually?

And then, after that’s done, there will be empty lines in the spreadsheet for the removed entries. I’m not sure if this is a problem, but I’m curious if there’s a way to get the spreadsheet to “compact” itself so that there are no blank lines throughout. I have a feeling that empty lines may screw up with whatever automated process their company uses to send out our letters.

The deletion of the duplicates is the most important. I really don’t want to sit here for an hour analyzing every line manually.

2

If the patient data is in a database, you can do it through SQL statements.

If the patient data is a flat list in a spreadsheet, see Jellyfish’s insightful post.

For the record, patient data really should be in a database, not a spreadsheet. You can run a SQL query to just get the list of patients that don’t have duplicates. For example: SELECT DISTINCT lastname, address FROM patients;

3
  1. First sort the rows alphabetically.
  2. Then add a new column with the following command…
    If(a2==a1,1,0)
    Or the equivalent in open office. ‘a’ is the column name for the column with the name. I.e., the column that you sorted alphabetically.
  3. Copy this new column and then paste special, as values, overwriting the formula.
  4. Sort by the new column.
    (If you didn’t paste as values, then they would ninja-edit themselves here.)
  5. Delete all the ‘1’ rows. (will all be at the top or bottom). The duplicates are gone.
  6. Now you are free to re-sort however you wish and to delete the new column.
  7. Get a big raise.
4

The file was sent to me as an Excel sheet, not generated in-house, and I don’t know how to work with databases, just to clear that up.

jellyfish that sounds logical, thanks man! Can’t wait to try it out.