I am asking for help in creating a formula that will give the results in the yellow box in cells D2 to O12 in the attached table.
Description of the formula:
1. In column "A" "TYPE NUMBER" there are 3 different digits (target n-digits) and, for example: for "TYPE NUMBER" with the value "1" (in cells: A7, A11, A12) in column C "SEQUENCE NUMBER" digits exist: 6, 8 and 1 (in cells: B7, B11, B12). The formula should rank the numbers from the smallest to the largest, i.e. assign them the numbering:
6-> 2
8-> 3
1-> 1
2. In addition, the formula should, due to the date in column "C" "MONTH OF STOP OF NUMBERING" compared with one of the cells D1: O1 stop counting the row in the next month after "MONTH OF STOP OF NUMBERING".
Examples:
a) From "TYPE NUMBER" in column "A" with the value "1" the formula calculates the following order: D12 = 1, D7 = 2, D11 = 3
b) From the "TYPE NUMBER" column "A" with the value "2", the formula calculates the following order: D5 = 1
c) From "TYPE NUMBER" in the column "A" with the value "3", the formula calculates the following order: D3 = 1, D9 = 2
d) Because "MONTH OF STOP OF NUMBERING" in cell C12 is February, the formula ends numbering in February in cell E12. Because this is the first occurrence of "TYPE NUMBER" and "SEQUENCE NUMBER", therefore in D12 = 1 and E12 = 1.
e) In C7 there is no "MONTH OF STOP OF NUMBERING", so the numbering is displayed in this row until December D7: O7, but since March (F7) this is the first occurrence of "TYPE NUMBER" = 1 (no value in F12 : O12), then the value 1 in F7: O7 is displayed.
f) analogy, eg in D11: E11 = 3, because this is the third occurrence of "TYPE NUMBER" 1 in January and February, and from March to November it is the second occurrence (F11: N11). In December, nothing appears in O11 because the date in C11 (November date) ends the numbering in November.
I'm sorry if I made mistakes in English.
Description of the formula:
1. In column "A" "TYPE NUMBER" there are 3 different digits (target n-digits) and, for example: for "TYPE NUMBER" with the value "1" (in cells: A7, A11, A12) in column C "SEQUENCE NUMBER" digits exist: 6, 8 and 1 (in cells: B7, B11, B12). The formula should rank the numbers from the smallest to the largest, i.e. assign them the numbering:
6-> 2
8-> 3
1-> 1
2. In addition, the formula should, due to the date in column "C" "MONTH OF STOP OF NUMBERING" compared with one of the cells D1: O1 stop counting the row in the next month after "MONTH OF STOP OF NUMBERING".
Examples:
a) From "TYPE NUMBER" in column "A" with the value "1" the formula calculates the following order: D12 = 1, D7 = 2, D11 = 3
b) From the "TYPE NUMBER" column "A" with the value "2", the formula calculates the following order: D5 = 1
c) From "TYPE NUMBER" in the column "A" with the value "3", the formula calculates the following order: D3 = 1, D9 = 2
d) Because "MONTH OF STOP OF NUMBERING" in cell C12 is February, the formula ends numbering in February in cell E12. Because this is the first occurrence of "TYPE NUMBER" and "SEQUENCE NUMBER", therefore in D12 = 1 and E12 = 1.
e) In C7 there is no "MONTH OF STOP OF NUMBERING", so the numbering is displayed in this row until December D7: O7, but since March (F7) this is the first occurrence of "TYPE NUMBER" = 1 (no value in F12 : O12), then the value 1 in F7: O7 is displayed.
f) analogy, eg in D11: E11 = 3, because this is the third occurrence of "TYPE NUMBER" 1 in January and February, and from March to November it is the second occurrence (F11: N11). In December, nothing appears in O11 because the date in C11 (November date) ends the numbering in November.
| Excel Workbook | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | TYPE NUMER | SEQUENCE NUMBER | MONTH OF STOP OF NUMBERING | 2018-01-31 | 2018-02-28 | 2018-03-31 | 2018-04-30 | 2018-05-31 | 2018-06-30 | 2018-07-31 | 2018-08-31 | 2018-09-30 | 2018-10-31 | 2018-11-30 | 2018-12-31 | ||
| 2 | |||||||||||||||||
| 3 | 3 | 2 | 2018-08-20 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||
| 4 | |||||||||||||||||
| 5 | 2 | 22 | 2018-06-12 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||
| 6 | |||||||||||||||||
| 7 | 1 | 6 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||
| 8 | |||||||||||||||||
| 9 | 3 | 10 | 2018-01-05 | 2 | |||||||||||||
| 10 | |||||||||||||||||
| 11 | 1 | 8 | 2018-11-05 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | |||
| 12 | 1 | 1 | 2018-02-15 | 1 | 1 | ||||||||||||
Sheet | |||||||||||||||||
I'm sorry if I made mistakes in English.
Last edited:
.
