Deleting duplicates V2 (on demand)
Trying an easier version to explain how to detect duplicates on spreadsheets and then delete them in the segment timer.
Using formulas and not macros (newbie in macros).
1. Gauge the interest in deleting duplicates first
First, you have to see if that’s useful for 1 or 2 duplicates, it’s still okay to let them.
Then, see if the CM wants to do it, I think. Because if the CM doesn’t want to, not much choice than letting duplicates.
To help you decide:
- Duplicate subs can only be deleted by the CM normally, except for some dramas where we got lucky and the English mod can delete segments.
- In the segment timer, maybe you can try by copying 1 sub somewhere and then delete the segment so you can check if like Husbands you can delete a segment as a mod and not as a CM.
- It doesn’t impact the Learn Mode to have duplicates. It’s just like another language where you have the same sub twice in a row, you just copy the same sentence twice from the script.
- It doesn’t impact the viewer exp in the normal viewer mode, there’s no difference for them with or without duplicates. They see the same sub.
- The only difference is for subbers: you have to copy paste the same sub.
If the CM agrees and you still want to do it, continue to read.
If you know how to use a sheet in general, call a cell, how to type a function, differences between functions for numbers and letters, the function "if" and the symbols "<>" and "", then skip to the following post **3. Deleting duplicates on Viki**.
**2. Using a spreadsheet (googlesheet for free, microsoft excel not free, etc.)**
Using: A sheet is like a chessboard or a naval battle.
Content: A sheet is not so different than a document/word file. You just type info on it.
Googlesheets vs. googledocs: formulas, tables, it’s easier to work with sheets. It will make you gain time thanks to formulas.
A sheet has:
-columns called by letters: columns A, B, C, etc.
-lines called by numbers: line 1, 2, 3, etc.
-cells = intersection of columns and lines: cell A1 is the intersection of column A and line 1.
Add, delete columns and lines as you like.
Number 10 is in… cell A1.
Cell A2 contains… the number 11.
It’s like a naval battle game.
So now, you know how to call a cell. It’s the cell reference.
b1. Introduction to formulas with numbers:
A sheet can:
- calculate thanks to formulas, also called “functions” that developers created inside sheets.
Important rule #1: A formula/function always begins with the symbol equal = at the beginning of a cell.
When you type = at the beginning of a cell, it means you’re calling out a formula/function.
Let’s try adding numbers thanks to formulas.
Goal: In A3, I want to obtain the sum of 10 and 11 = I’m going to add A1 and A2 in short (let’s call cells directly by their cell reference whatever they have inside).
If I add cell A1 and cell A2, what to type in A3?
- Either, I’m going to type as if I’m writing the calculation: =A1+A2
- Either, instead of typing A1 and A2, I’m just going to be lazy and click directly on cells A1 and A2.
You can see in the bar fx (means “function”) the formula you just typed. You can also type/edit the formula in the fx bar.
Be careful, if you go on cell A1 and A2, you will see just 10 and 11 in the fx bar, because we didn’t type any formula in these cells, but we just wrote “hard” numbers.
If you go on cell A3, you will see the formula in the fx bar, but the cell A3 itself will show the result of the formula.
When you tap enter, you obtain the result of the formula you just typed.
b2. Function sum, etc.
Okay, so now, you know how to add numbers. Good news, it’s the same principle with subtraction, multiplication, division…
Imagine you have 10 numbers to add, will you click on 10 cells to add them together?
Second good news, the function “sum” will add them all at once for you
In formulas sheets, to go from cell… to cell…, we use colons “:”, we’re not going to type “A1 until A10” in a formula, but “A1:A10”. So the function sum is:
=sum(A1:A10) => add content from cells A1 to A10.
As you can see:
- Any function created by developers of spreadsheets looks like =functiontitle(…).
- A function begins with the symbol equal and ends with a closing parenthesis )
- Inside parentheses (…), it’s cell references or numbers or we’ll see that in a few minutes letters, words and even a function inside another.
Back to sum function:
Result: A11 = 145
Imagine you will change numbers inside A1 until A10. Will the function will automatically calculate with updated numbers? Yes, because with cell references, a function will search the content of a cell.
Hence, the interest of using cell references and not “hard” numbers.
List of functions in googlesheets if you want to try them out:
c. Introduction to formulas with letters:
And if sheets functions not only work with numbers, but with letters or even better words and sentences?
Important rule #2 for functions:
- to work with numbers, use mathematical symbols (+,-,/,*, <, >)
- to work with letters, use other symbols such as & and quotation marks ""
Let’s replace numbers 10 and 11 in Ex 1 by letters a and b.
Same cells, but different content.
And now, we would want to “sum letters” to have “ab”.
Combine letters with & (a formula with letters or numbers will always begin with = as we said in rule #1)
**c1. Introducing the function if and the symbols different <> and empty ""**
c1.1 <> and ""
-The opposite of equal is different.
In formulas sheets language, equal is = and different is… <>
- oblivion or emptiness is represented by quotation marks “” in formulas sheets.
If in a sentence implies a condition:
If it will be sunny, I will water my flowers outside or else I stay at home.
In formulas sheets, it’s the same thing:
=If(it will be sunny, I will water my flowers outside, or else I stay at home).
=If(condition 1 is true, then result 1, or else result 2).
The function if contains 3 parts inside parentheses, each part being separated by a comma.
Now, if we want to change the formulation:
If it will be sunny, I will water my flowers outside or else nothing.
=If(“sunny”, “water flowers”, “”).
Prefer convenient small words than long sentences in a formula sheet.
As you can see:
- When you type words in a formula (and not a cell reference), you will have to use quotation marks to mention a “word”.
- When you type your formula, you have to indicate the cell reference of condition 1.
Let’s imagine a situation with days of the week and next to it, we have the weather forecast.
Goal: put a reminder for you to know when to water flowers.
1st column - A: days of the week
2nd column- B: weather
3rd column- C: your formula =If(“sunny”, “water flowers”, “”)
Type your formula =If(B1=“sunny”, “water flowers”, “”) in C1, then drag the period at the down right corner until C7 so the formula is applied in C2, C3… in column C.
So now, you know how to use “if” and the symbol nothing “”.
You can obtain the same result if you replace sunny by different than rainy, it’s the same meaning.
With an English sentence:
If the weather is not rainy, then I will water my flowers outside, or else I stay at home.
With a formula sheet:
=If(A1<>“rainy”, "water flowers, “”)
So now, you know how to use the conditional function “if” and the symbols <> (different) and “” (empty).
You will use the same functions to detect duplicates.