Spreadsheet help with creating a formula?

Asked by ariherzog (33

) September 29th, 2009

Using OpenOffice Calc, I have a table with 5000 records. About 50% have the same last name and street address. I’d like to run a query that only shows one record if LN and address are the same, using the first name as the differentiator. There is also a unique key in each record.

For instance, if Tom Smith and Jane Smith live at 25 Anywhere Street, I want the query results to only show one Smith at 25 Anywhere Street, ideally concatenating the two first names into a new field; else deleting one of them.

Thoughts?

Observing members: 0

Composing members: 0

3 Answers

Try the spreadsheet in Google Docs. The formulas are super easy to use.

thanatos (324

)“Great Answer” (0

) Flag as…

I’m not that familiar with OpenOffice’s Functions, but I’ll tell you how I would do it in Excel. The concepts should be the same.

Question: is it possible to have 3 records you would want to combine?

- Sort the records by last name and address
– for the sake of explanation, First Name is in the A column, Last Name is in the B column and Address is in the C column. Row 1 is the headers
– In column D you’ll have the equation.
– Starting in cell D3 you’ll have the following equation:

=if(and(B2=B3,C2=C3),A2&” and ”&A3,if(and(B3=B4,C3=C4),“Delete”,A3))

This equation will give you the new combined first names or just the current records first name and will tell you which records to later delete (Or if you just want to hide them). If you are going to delete the records, make sure you copy and paste the new first name fields as values.

If you have a situation with 3 records, you would need to add additional logic statements.

se_ven (789

)“Great Answer” (0

) Flag as…

That doesn’t work. Gives an error 508.

ariherzog (33

)“Great Answer” (0

) Flag as…