Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.