How to combine value from 3 different sheets?

[remo00]

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]

Merge everything into one list and things become easy.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[remo00]

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]

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]

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]

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.