[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
remo00
OOo Enthusiast

Joined: 21 Dec 2006
Posts: 117

 Posted: Fri May 15, 2009 12:08 am    Post subject: How to combine value from 3 different sheets? Hello, I don't know if this is best done with calc or base but this is my goal. I have 3 data sets (calc sheets) with three fields Student name, year, school name. Table A and B are not overlapping; that is the same student in the same year it is in only one of the two sheets. Table C contains 90% of the data also present in the other 2, somethimes with the school name badly written, and 10% of data usefull. I need to combine all data in a single sheet. I was thinking of creating a unique entry (a sort of primary key) combining Student name and year, and then doing a VLOOKUP with some IF conditions to search first in Table A then in table B then in Table C. Does it make sense or is there a better solution? Cheers
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Fri May 15, 2009 1:59 am    Post subject: Merge everything into one list and things become easy._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
remo00
OOo Enthusiast

Joined: 21 Dec 2006
Posts: 117

 Posted: Fri May 15, 2009 2:36 am    Post subject: Hi Villeroy, Thanks for your replay. If I put everything in 1 sheet which is the best way to deal with duplicate? Since Table C is the less reliable, I guess I should add another coloumn with the Table of data origin (A, B, C), and use data with C only if data on Table A and B are not available. Any suggestion? Cheers.
BillDrew
General User

Joined: 24 Apr 2009
Posts: 5
Location: NW Wisconsin

 Posted: Fri May 15, 2009 5:34 am    Post subject: After sorting on the relavent data, add a colum with =IF(AND(a4=a5;b4=b5;...);"*";""). That will produce an asterisk in the second (and subsequent) row(s) if there are duplicates. Freeze those (substitute values for the formula) with a copy-paste special, sort on that column, and delete those rows with an asterisk. The same idea can be used to identify those that have data in C but not in A or B. If you have mcuh more than several hundred rows, BASE would be a better way to deal with this.
remo00
OOo Enthusiast

Joined: 21 Dec 2006
Posts: 117

 Posted: Fri May 15, 2009 8:43 am    Post subject: Thanks BillDrew, You showed me a very nice trick. Sorting data by student, year, table, I have data in the right order so entries from table C are used only when essential. Table A, B and C together are almost 10.000 rows, so I guess Base would be better. In fact the resulting table should be the main table for a database. How do I perform the same trick in base? I know almost nothing about SQL Inserting everything in 1 table, I found the following code to delete duplicate but it doesn't work. Somebody could tell me why? delete from main_table where (source, student, year) not in ( select min(source), student, year from main_table group by student, year);
BillDrew
General User

Joined: 24 Apr 2009
Posts: 5
Location: NW Wisconsin

 Posted: Fri May 15, 2009 11:45 am    Post subject: Sorry, can't offer any help with Base since I am just beginning to figure it out myself. Was pretty good with dBase and FoxPro programing but retired before figuring out SQL so haven't had much reason to get really serious about it.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum