bansche123
New Member
- Joined
- Dec 9, 2019
- Messages
- 13
- Office Version
- 365
- Platform
- Windows
Dear Forum Users,
I would like to kindly ask for help regarding creating a formula or formulas.
As you can see in the file there are a over a dozen tables with 5 numbers in each row in them (the numbers are identical in each of the 14 tables). You can also spot cells designated R1, R2…, R14.
R stands for Repetition, the number next to the R describes the row to which the comparison is made in order to find repetitive numbers.
Next to, for example, R1 in the K column you’ve got the total number of given repetitions in each row — 100% accurate you can check this way whether your formula is right.
Examples: R1 means that A2;E2 is compared to A3;E3, R2 means that A2;E2 is compared to A4;E4, R3 means that A2;E2 is compared to A5;E5 and so on until R14
Let's stay at R1 at first:
The aim is to look for numbers in A2;E2 which repeated themselves in A3;E3. In this specific case none did, but if you compare A4;E4 to A5;E5, there are two numbers in A4;E4 that repeat themselves in A5;E5. Those numbers shall be identified by the formula.
But that’s not all. Next to the table with the numbers there is a representation of the repetitions in which the value 1 is assigned to the numbers that have repeated themselves and the value 0 marks the numbers that did not repeat themselves. As you can see the zeros and ones are in the same order as the numbers are in a given row of the table. Essentially that is the whole point of the formula.
Very important! R2-14 are slightly different
The core idea stays the same, but the formulas for R2-R14 need to include a mechanism that would scan the whole table and eliminate duplicates in repetitions.
Example: R4, in the 16h row: AK16;AO16, so you go 4 rows down and check for repetitions,
In AK16;AO16 you’ve got 7, 13, 18, 20, 32
In AK20;AO20 you’ve got 7, 19, 20, 33, 34
You see that 7 repeats itself and 20 repeats itself, but in the AU column which is 100% proofread you see that there should be only 1 repetition, so how do you determine whether 7 or 20 is the right one?
Basically, it’s necessary to realize that 7 was already taken into consideration as a repetition in R1, so that leaves you with the 20. So, summing up only the latest repetitions shall count, that is why a mechanism that would scan the whole table and eliminate duplicates in repetitions is needed for R2-R14.
In order to demonstrate everything I have described in this post I prepared this file having done everything by hand to give an illustration of what I have been talking about. I hope it helps to understand what I meant.
Thank you in advance
I would like to kindly ask for help regarding creating a formula or formulas.
As you can see in the file there are a over a dozen tables with 5 numbers in each row in them (the numbers are identical in each of the 14 tables). You can also spot cells designated R1, R2…, R14.
R stands for Repetition, the number next to the R describes the row to which the comparison is made in order to find repetitive numbers.
Next to, for example, R1 in the K column you’ve got the total number of given repetitions in each row — 100% accurate you can check this way whether your formula is right.
Examples: R1 means that A2;E2 is compared to A3;E3, R2 means that A2;E2 is compared to A4;E4, R3 means that A2;E2 is compared to A5;E5 and so on until R14
Let's stay at R1 at first:
The aim is to look for numbers in A2;E2 which repeated themselves in A3;E3. In this specific case none did, but if you compare A4;E4 to A5;E5, there are two numbers in A4;E4 that repeat themselves in A5;E5. Those numbers shall be identified by the formula.
But that’s not all. Next to the table with the numbers there is a representation of the repetitions in which the value 1 is assigned to the numbers that have repeated themselves and the value 0 marks the numbers that did not repeat themselves. As you can see the zeros and ones are in the same order as the numbers are in a given row of the table. Essentially that is the whole point of the formula.
Very important! R2-14 are slightly different
The core idea stays the same, but the formulas for R2-R14 need to include a mechanism that would scan the whole table and eliminate duplicates in repetitions.
Example: R4, in the 16h row: AK16;AO16, so you go 4 rows down and check for repetitions,
In AK16;AO16 you’ve got 7, 13, 18, 20, 32
In AK20;AO20 you’ve got 7, 19, 20, 33, 34
You see that 7 repeats itself and 20 repeats itself, but in the AU column which is 100% proofread you see that there should be only 1 repetition, so how do you determine whether 7 or 20 is the right one?
Basically, it’s necessary to realize that 7 was already taken into consideration as a repetition in R1, so that leaves you with the 20. So, summing up only the latest repetitions shall count, that is why a mechanism that would scan the whole table and eliminate duplicates in repetitions is needed for R2-R14.
In order to demonstrate everything I have described in this post I prepared this file having done everything by hand to give an illustration of what I have been talking about. I hope it helps to understand what I meant.
Thank you in advance
