Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Bianca777 Profile
 | Femeie
19 ani
Brasov
cauta Barbat
19 - 61 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
I have a list of suppliers and would like to count them in order of delivery date, but also have unique values - only new suppliers will be added to the count.
I have my suppliers in column G starting from G6, and I am adding the Rank in column N.
In N6 I have the following formula:
| =IF(COUNTIF($G$6:G6;G6)=1;MAX($N$5:N5)+1;"") |
N5 is a cell that contains table header, the string: Rank.
Let's see how this works:
Row 6 (first row in my table):
- the first supplier appears in G6
- COUNTIF checks range $G$6:G6 for the value in G6 and finds 1 occurrence for the first supplier
- since it's the first occurrence of the supplier, COUNTIF function is true (1 is equal to 1)
- MAX of range $N$5:N5 is 0 since the value of N5 is a word
- MAX + 1 = 1, so the rank of my first supplier returned in N6 is 1
Row 7:
- the first supplier appears again in G7
- COUNTIF checks range $G$6:G7 for the value in G7 and finds 2 occurrences for the first supplier
- since it's the second occurrence of the supplier, COUNTIF function is false (2 is not equal to 1)
- since COUNTIF is false, cell N7 returns "" (empty)
Row 8:
- the second supplier appears in G8
- COUNTIF checks range $G$6:G8 for the value in G8 and finds 1 occurrence for the second supplier
- since it's the first occurrence of the supplier, COUNTIF function is true (1 is equal to 1)
- MAX of range $N$5:N7 is 1 since there is the value 1 in cell N6
- MAX + 1 = 2, so the rank of my second supplier returned in N8 is 2
And so on.
Note: the Ranking for Supplier will NOT be kept if you sort the list in a different way.
_______________________________________
|
|
| pus acum 1 an |
|