| B | C | D | E | F | |
| 1 | ort | ||||
| 2 | 1 | AAAAAAAAAAAAAAA | |||
| 3 | 2 | GGGGGGGGGGGGGG | |||
| 4 | 3 | sssssss | 1 | AAAAAAAAAAAAAAA | |
| 5 | 4 | UUUUUUUUUUU | 2 | Axel | |
| 6 | 5 | TTTTTTTTTTTTTTTTTTT | 3 | GGGGGGGGGGGGGG | |
| 7 | 6 | JJJJJJJJJJJJJJJJ | 4 | sssssss |
| Formeln der Tabelle | ||||
| ||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! |
| A | B | C | D | E | F | G | H | |
| 1 | Axel | Ort | ||||||
| 2 | AAAAAAAAAAAAAAA | |||||||
| 3 | AAAAAAAABBBBBBBBB | Ort1 | Ort2 | |||||
| 4 | GGGGGGGGGGGGGG | AAAAAAAAAAAAAAA | Axel | |||||
| 5 | sssssss | Axel | AAAAAAAABBBBBBBBB | |||||
| 6 | UUUUUUUUUUU | GGGGGGGGGGGGGG | Axel | |||||
| 7 | UUUUUUUUUUU | sssssss | GGGGGGGGGGGGGG | |||||
| 8 | TTTTTTTTTTTTTTTTTTT | Axel | sssssss | |||||
| 9 | JJJJJJJJJJJJJJJJ | UUUUUUUUUUU | Axel | |||||
| 10 | OOOOOOOOOOOOOO | Axel | UUUUUUUUUUU | |||||
| 11 | KKKKKKKKKK | DDDDDDDD | WWWWW | |||||
| 12 | RRRRRRRRRRRRRRRRRRRRR | TTTTTTTTTTTTTTTTTTT | Axel | |||||
| 13 | RRRRRRRRRRRRRRRRRRRRR | Otto | TTTTTTTTTTTTTTTTTTT | |||||
| 14 | Axel | JJJJJJJJJJJJJJJJ | ||||||
| 15 | Axel | OOOOOOOOOOOOOO | ||||||
| 16 | KKKKKKKKKK | Axel | ||||||
| 17 | OOOOOOOOOOOOOO | Fritz | ||||||
| 18 | RRRRRRRRRRRRRRRRRRRRR | Axel | ||||||
| 19 | Axel | RRRRRRRRRRRRRRRRRRRRR | ||||||
| 20 |
| Formeln der Tabelle | ||||
| ||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||
| Matrix verstehen |
| BA | BB | BC | |
| 1 | Stolpe-Dreifurt e.V | ||
| 2 | 1 | DevilLegend | |
| 3 | 2 | Herta Aken 0815 | |
| 4 | 3 | FC Leknica | |
| 5 | 4 | PNK Raciborz RD | |
| 6 | 5 | BS Nike 1909 | |
| 7 | 6 | FC Pfulliwood | |
| 8 | 7 | KP Odra Wodzisław Śląski | |
| 9 | 8 | Dynamo Dortmund | |
| 10 | 9 | 1.FC Gran Canaria | |
| 11 | 10 | 1. Fc Dixikloo | |
| 12 | 11 | ESV Horrem | |
| 13 | 12 | Mc Poland | |
| 14 | 13 | Polonia Elsen | |
| 15 | 14 | Schlaatzer FC | |
| 16 | 15 | FC PICPIWO | |
| 17 | 16 | 1.FC Zgorcelez | |
| 18 | 17 | GD Ansk |
| verwendete Formeln | |||
| Zelle | Formel | Bereich | R1C1 für Add In |
| BC2 | {=LINKS(INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)));FINDEN(" (";INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)))))} | $BC$2 | =LEFT(INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))),FIND(" (",INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))))) |
| BA | BB | BC | CB | CC | CD | CE | |
| 1 | Stolpe-Dreifurt e.V | Heim | Gast | ||||
| 2 | 1 | DevilLegend | 1 | 1. Fc Dixikloo (110) | Stolpe-Dreifurt e.V (86) | ||
| 3 | 2 | Herta Aken 0815 | 2 | Stolpe-Dreifurt e.V (86) | DevilLegend (56) | ||
| 4 | 3 | FC Leknica | 3 | ESV Horrem (93) | Stolpe-Dreifurt e.V (86) |
| verwendete Formeln | |||
| Zelle | Formel | Bereich | R1C1 für Add In |
| BC2 | {=LINKS(INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)));FINDEN(" (";INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)))))} | $BC$2 | =LEFT(INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))),FIND(" (",INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))))) |
| CB3:CB4 | =CB2+1 | =R[-1]C+1 | |
| CC2:CC4,CE2:CE4 | =dATEN!CE2 | =dATEN!RC[2] | |
Die Datei https://www.herber.de/bbs/user/88311.xlsx wurde aus Datenschutzgründen gelöscht
| AZ | |
| 2 | DevilLegend |
| verwendete Formeln | |||
| Zelle | Formel | Bereich | R1C1 für Add In |
| AZ2 | =LINKS(BY2;FINDEN(" (";BY2)-1) | =LEFT(RC[25],FIND(" (",RC[25])-1) | |