PROJECT #320 -- TRANSLATION THESAURUS For OPEN OFFICE
XLS procedure to make "th_en_BE.dat" file from master "TransXYZ.xls"

Thesaurus.doc						10Sept2006

Repeat ten times from master files TransABC to XYZ.xls
Master Files are five cols wide
A	B: C: D: E:
Word, Definitions1-4
Open workbook such as ThesWork.xls
Copy cols A:E: from TransXYZ.xls into A:E:

PRE=PROCESSING of TransX.xls
F-J   =TRIM([A1)		(typing space for delete in Excel inserts 80 spaces or such)
Apply to F1:J1
Apply to F1:J1 to cntl End
Copy, paste special – values , back into A:E:
Delete col F:J

CREATE DATA LINE 1
F1    =A1&”|1”
Apply to F1:  (May have to retype M$Word Double Quote marks)
Copy col F; Paste Special – values  back into col A
Delete Col F

CREATE DATA LINE 2
F1	=B1 & IF(LEN(C1)=0,"","|"&C1) & IF(LEN(D1=0),"","|"&D1) & IF(LEN(E1)=0,"","|"&E1)
Copy F:: Paste Special – value back on B
Delete columns C:E
C2 	=IF(EXACT(A2,A1),"delete me","")    [skip C1]
D1	=IF(EXACT(A2,A1),B1&"|"&B2,B1)
Apply to all rows of each column:
Copy D:: Paste Special – value back on B
Delete col D:

CONCATINATE OVERFLOW from 2nd XLS ROW IN MASTERFILES
Manually find “delete me” and do it (delete the second rows)
Or. if to be retained, delete the concatenation of the upper one
Write it down for manual fixes “|2” in a moment

SORT-MERGE-OUTPUT
Insert Column B. – Populate with odd numbers 1,3 5, …
Column D – populate with even numbers 2, 4, 6 . . . 
Copy C:D to the end of A:B 
DATA, Sort on B.
Delete Col B, C, D
SAVE as ThesX.txt   (tab delineated)

MANUAL FIXES
Open with Editpad
1.  Find (double quote) and replace with (nothing) ALL
2.  Find the two-liners written down
	Change Word|1 to Word|2
	Delete the second instance of Word1