Calc: How can I compare the words on a line to the words on another line and count the number of words, which are the same?

[Laniol]

Hello LibreOffice-Community.

This is the first question I ask, please forgive me If I did something wrong.

What I have is a .ods file with many sentences, with each word in its own cell. I’ll make up a short example

1. I want to go home
2. I wish to go home
3. Now I want to go home and sleep
4. I like this
5. Go want home to I

What I need to do, is to grade each of 2-5 according to their correctness in repeating 1.
So, the sentence 2 should get marked as 1 mistake (or 4 points).
Additional words do not matter and missing words count as wrong, so the sentence 3 should be graded as 0 mistakes (or 5 points), and the sentence 4 as 4 mistakes (or 1 point).
The order of words does not matter at all, so sentence 5 should still be graded as 0 mistakes or 5 points

The basic building-block of a solution would probably constitute something, that reads all cells in one line (or let’s say from C3 to AN3) (the correct sentence) and see, how many of those words appear in another line (let’s say from C4 to AN4) (the sentence to be graded). This number would constitute the “points” I need.

Amount of data:
Each correct sentence is 18 words long. There are 40 different sentences, each of which has a correct version and 16 different repetitions. But if a solution can correctly grade the above example, it should also handle the real data, I assume.

Thanks in advance for any answers. If more detail is needed I will gladly provide it.

Edit Basic Idea (?):
I think, one way to do it is by creating nested loops. I’ll try to put it in pseudocode, since I don’t know LibreOffice Basic

For Counter1 (call it N) = 1 to (Number of words in correct sentence, namely 18)
    For Counter2 (call it M) =1 to (Number of words in the sentence to be graded, namely 34) 
        Compare the Nth Word in the correct sentence to the Mth word in the current sentence. 
        If they are equal: Points = Points+1

Is this a plausible approach? If so, how can I express it into Calc?

Edit2: Here follows a more precise example of what I want to achieve. What I have is comparable to the following table, but without the Points

Person    Sentence Word1    Word2    Word3    Word4     Word5       Points
correct     1        I       want     to       go       home     
correct     2      This      is       a        short    sentence
correct     3        I       like    Linux     a        lot
1           1        I       wish     to       go       home           4
1           2      That      is       a        long     sentence       3
1           3        I       like    Linux     very     much           3
2           1        I       go      home      want     very           4
2           2     Sentence   long                                      2
2           3      Linux     is      very      strange                 1

So each of the persons 1 and 2 read the correct sentences and then repeated them more or less exactly. I need to find out how exactly, by counting how many of the words in the correct sentence the person repeated (in any order). This number constitues the Points.

Edit3 I upload an example of grading the same word twice. Oh no, I’dont, needs 3 points…

[mariosv]

Please attach/share a sample file with instructions about what can achieve and what must be the result?

[mariosv]

I think the attach file does what you want. with a formula like:

=SUMPRODUCT(LOWER(OFFSET($C$1:$G$1;$B5;0))=LOWER(OFFSET($C5;0;ROW(A$1:A$5)-1)))

OFFSET($C$1:$G$1;$B5;0) sets up the phrase for search.
OFFSET($C5;0;ROW(A$1:A$5)-1) forces to search word by word in the phrase, ROW(A$1:A$5) is used as counter and the trick to force search every word in the all phrase not only in the same position.
LOWER() avoids differences with the case.

CountPositivesWordMatches.ods

[Laniol]

This looks great! Much more elegant than my solution. I will try it out tomorrow. Thanks a lot for you answer! I’d vote you up, but for that >5 points are needed.

Edit Unfortunately, your solution suffers from the same problem my did. If a correct sentence contains the same word twice, but the written sentence only once, it awards two points for that one word. You can try that by changing the first correct sentence to “I want to go go”. I’ll upload an example to my question.

[Laniol]

I came up with a macro that does what I want. However, I assume that the solution provided by mariosv works. It is much much shorter and therefore probably better. I will still post the macro though, even if only for educational purposes. This is my first ever macro in any office suite. I’d upload the file, but it tells me I need >3 points to do that.

Edit I had a bug which made it so that it counted the same word twice if it appeared twice in the correct sentence. So if the correct sentence was “I like you and you like me”, but someone had written “I like you”, it would give him 4 points. This is now corrected by the help of colouring the cells that already gave a point and ignoring them afterwards. Here is the new version

Sub MatchingWords

'Macro for counting matching words
'Preparation: Remove points, spacec and commas. Turn all letters lowercase (Select all, Format)
'Care for whether the correct sentence is above or below the corresponding other sentences. Read the comments in the macro.
'Sort according to itemname, don't use filters. Make sure you can revert this sorting later by sorting according to Participant-Numbers
'Execution: Select the first cell of the correct sentence, then run macro

'If you need to rerun it over the same sentences, make sure to set the previous results in the corresponding cells to 0!
'Otherwise it will add them to the new points
'TODO Make sure it counts every word in the written sentence just once! 
'So if the correct sentence has two "und", but the written sentence just one, it should only give one point
'Done

Dim Doc As Object
Dim Sheet As Object

Dim Cell  As Object
Dim Cell2 As Object
Dim Cell3 As Object

Dim coword As string 'correct word
Dim chword As string 'checked word

Dim co1 as Long 'counter 1
Dim co2 as Long 'counter 2
Dim co3 as Long 'counter 3

Doc = ThisComponent
Sheet = Doc.Sheets(0) 'Works on the first sheet!

dim xco as Long 'coords of selection
dim yco as Long

Dim oCellRange As Object    
oCellRange = ThisComponent.getCurrentSelection() 

yco = (oCellRange.getCellByPosition(0, 0).getCellAddress().Row)    'y coord
xco = (oCellRange.getCellByPosition(0, 0).getCellAddress().Column)  'x coord

For co1 = 0 to 17 'loop through the correct sentence (17 = one less than the number of word in the correct sentence)
	Cell = Sheet.getCellByPosition(xco+co1,yco)
	coword=Cell.String
	
	for co2 = 1 to 8 'loop through the people (8 = number of people [not one less!])
			
		for co3 = 0 to 37 'loop through the sentenc a person said (37 = one less than the maximal amount of words)
			Cell2 = Sheet.getCellByPosition(xco+co3,yco-co2) 'change -co2 to +co2 , if the correct sentence come before its corresponding sentences
			chword = Cell2.String 
			Cell3 = Sheet.getCellByPosition(xco+50,yco-co2) 'change -co2 to +co2 , if the correct sentence come before its corresponding sentences
			if coword = chword and Cell2.CellBackColor <> RGB(0,255,0) then 'The number 50 in the line above tells the maxro where to write the points
				Cell3.value = Cell3.value + 1   
				Cell2.CellBackColor = RGB(0,255,0)             
				exit for									 
			end if
		next
	next
next

End Sub