OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to combine value from 3 different sheets?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
remo00
OOo Enthusiast
OOo Enthusiast


Joined: 21 Dec 2006
Posts: 117

PostPosted: Fri May 15, 2009 12:08 am    Post subject: How to combine value from 3 different sheets? Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 15, 2009 1:59 am    Post subject: Reply with quote

Merge everything into one list and things become easy.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
remo00
OOo Enthusiast
OOo Enthusiast


Joined: 21 Dec 2006
Posts: 117

PostPosted: Fri May 15, 2009 2:36 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
BillDrew
General User
General User


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

PostPosted: Fri May 15, 2009 5:34 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
remo00
OOo Enthusiast
OOo Enthusiast


Joined: 21 Dec 2006
Posts: 117

PostPosted: Fri May 15, 2009 8:43 am    Post subject: Reply with quote

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);
Back to top
View user's profile Send private message
BillDrew
General User
General User


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

PostPosted: Fri May 15, 2009 11:45 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group