Automatic movement of data between cells and removal of duplicate cells

English
,
#1

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
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
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

#2

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. …

#3

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.

#4

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.

#5

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

#6

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 .

#7

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

#8

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.

#9

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.

#10

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”.

Attachment

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

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

#11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.