Ask Your Question

Automatic movement of data between cells and removal of duplicate cells [closed]

asked 2018-07-13 22:49:47 +0100

stuarts.burgers gravatar image

updated 2020-08-13 20:49:50 +0100

Alex Kemp gravatar image

Hi, I know what I would like to automate but difficult to put into words. I have a list of names and addresses, there are three options, the first option is that there are family members Mum Dad and Siblings, the second option is that there is a single person and the third option is that there seem to be flatmates or a couple that have retained their own surnames. refer FAMILIES RAW C:\fakepath\FAMILIES RAW.ods

What I would like to do is to create a Family Row for the first option, moving the first "Famly name" in the Surname Colum from the Surname Colum to the First Name Colum then in the Surname column Change the " Family name " to Family then delete the rows that contain the duplicate members of the family.

I do not wish to alter the rows that do not contain what would seem to be family members based upon duplicate Surnames. For the result, I desire refer to FAMILIES edited C:\fakepath\FAMILIES edited.ods

I see that there could be some problems with Van and van etc but could be easily overcome with "Text to Column".

I hope some nice person can help even if you get me started I would be grateful. Stuart

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-13 20:51:33.801529


I cannot see a way. Someone has to know first what "families" are there - and if they have empty intersections. You should be the one who knows best and enters the information, probably a common number in a dediocated column....
The only way the "intelligence" of a spreadsheet might use to guess "families" is exactly common addresses. However, if you type the addresses in one by one, there will certainly be typos or changes in any details. ...

Lupp gravatar imageLupp ( 2018-07-14 00:30:16 +0100 )edit

Thanks for your help, The list of names and address is taken from an electoral roll that is given to an Election Candidate. What we are attempting to do is to create as seen "Families edited.ods" (above) is a mail merge list so that if we do a mail shoot then for household with "Families" ( Mum Dad and Siblings)we send only one letter, for all other households we send individual letters to each person in the house hold.

stuarts.burgers gravatar imagestuarts.burgers ( 2018-07-14 03:00:34 +0100 )edit

We are on a very tight Budget and if we can halve the number of letters going out as in the above example we can do more mail shoots or have money to spend in other areas. The order of the list in the examples above is so we can get volunteers to hand deliver so saving on mailing costs.

stuarts.burgers gravatar imagestuarts.burgers ( 2018-07-14 03:03:45 +0100 )edit

Sorry, the Number in the above examples is the street or house number for each house, not the elector number.

stuarts.burgers gravatar imagestuarts.burgers ( 2018-07-14 03:08:17 +0100 )edit

Looking at Families Raw.ods my thoughts were If C3=C2 and A3=A2 then copy A2 to B2 and replace A2 contents with "Family" we then delete row 3

But this will not work as if the new A3 is the same as the old A3 it will look to A2 and see that it does not equal A2 instead, of course, it would be equal to B2 .it is the compacting of the Family Groups ( Mum Dad and Sibblings) into one entry is the problem .

stuarts.burgers gravatar imagestuarts.burgers ( 2018-07-14 03:29:34 +0100 )edit

For the other Household options, this would not be an issue as C3 would not equal C2 or A3 would not equal A2.

stuarts.burgers gravatar imagestuarts.burgers ( 2018-07-14 03:32:17 +0100 )edit

A bit earlier you seemed aware of the fact that surnames can neither be used positive nor negative as a condition for "same family". The only condition with a significant selectivity is "exactly same address". If this is sufficiently mapped to the "number" in your "raw", the demao attached to my answer schould help you.
To do such things by formulae in a spreadsheet is complicated and highly inefficient. Therefor: Use the interactive tools or write user code.

Lupp gravatar imageLupp ( 2018-07-14 08:34:08 +0100 )edit

Quoting: If C3=C2 and A3=A2 then copy A2 to B2 and replace A2 contents with "Family".
To "move" rows you need code or interaction! And original data shouldn't get destroyed anyway
However, you can use the column where I joined associated names somehow reversely: Empty for the "head", pointer to the "head" for other members of the supposed family. The crux is the selective condition.

Lupp gravatar imageLupp ( 2018-07-14 08:41:58 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-07-14 01:00:49 +0100

Lupp gravatar image

updated 2018-07-14 09:12:46 +0100

If your "number" already is a "family identifier", first sort by it, do as demonstrated in the attached dociument, first sheet, 'Copy' everything, 'Paste Special...' results only in a second sheet, rearrange everything as wanted, filter for "not empty" in the family column ...
You see?
Of couse you have still the problem with probably different addresse within a "family".


Only expected to work correctly with LibO version 5.4.4 or higher!

New attachment explaing my recent comment to the question.
(Please add comments to the answer here.)

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-07-13 22:49:47 +0100

Seen: 94 times

Last updated: Jul 14 '18