Re: [libreoffice-users] How to check rows for to replace a value for another in Calc?

At 17:45 07/08/2014 +0200, Daniel R. Miguel wrote:
> I have a problem in Calc. I get a lot of spreadsheets from various 
> sources and I have to compile one big sheet with the information of 
> every one of them. One of the problems I am facing is that there is 
> a lot of names for the same user. So, for example:
>
> PHONE EXTENSION    Name Sheet A
> 111                                John Doe

Is "Name Sheet A" a column heading, or do you mean column headed 
"Name" on Sheet A?

> EXT.        Name A        Name B
> 111        John Doe       J. Doe
> 111        John Doe        Jon Doe
>
> I would like a system where I could have only one row for person:
>
> EXT.    Name A            Name B    Name C    Name D
> 111    John Doe            J. Doe    Jon Doe    ...
> 112    Dorian Gray    D. Gray    Don Gray    ...
>
> And once the table is properly established, I would want to replace 
> every appearance of Name B, Name C with the value of Name A.

Once you have done that, you get back to an exact copy of Sheet A - 
except that you will have blanks for the name where an extension 
appears on one of the other lists but not on Sheet A. I can't imagine 
that that's what you really want.

Won't you need to use a certain amount of intelligence and discretion 
in deciding what name you actually want and dealing with 
discrepancies? In that case, wouldn't it be simpler to:
o Copy the material to a single sheet.
o If preferred, give the cells from each sheet a background colour to 
identify them.
o Sort the sheet by the "extension" column.
o Go through the material manually, choosing which of the multiple 
rows for each extension to retain, marking these with some random 
character in a new column. (Or you could choose to mark the rows you 
didn't need.)
o Sort the sheet again, this time by the new column.
o Delete the range of rows - now all adjacent - without the mark.
o Remove the background colours.

If this doesn't work, it may be that you have not given sufficient 
detail about your problem.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted