Hi everyone. Hope you're having a lovely day.
I found a formula on the Internet that sorts all values in a range alphabetically. It's this one:
I also have a formula that removes specific prepositions and articles (in Spanish) if F4 contains the value "Delete". It's this one:
My question is: is there a way to combine these two? I'd like to create a formula that sorts cells in a range with the ability to remove prepositions and articles during the sorting process. I tried with a LET function, but I can't figure out the way to blend them together. Here is a sample of what the sorted list looks like:
Thanks very much, guys!
I found a formula on the Internet that sorts all values in a range alphabetically. It's this one:
Excel Formula:
=IFERROR(INDEX($C$4:$C$1000,MATCH(SMALL(NOT($C$4:$C$1000="")*IF(ISNUMBER($C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000)+SUM(--ISNUMBER($C$4:$C$1000))),ROWS($A$2:A2)+SUM(--ISBLANK($C$4:$C$1000))),NOT($C$4:$C$1000="")*IF(ISNUMBER($C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000)+SUM(--ISNUMBER($C$4:$C$1000))),0)),"")I also have a formula that removes specific prepositions and articles (in Spanish) if F4 contains the value "Delete". It's this one:
Excel Formula:
=IF($F$4="Delete",IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C21," de los "," ")," de las "," ")," de la "," ")," del "," ")," de "," ")," el "," ")," la "," ")," los "," ")," las "," ")," y "," ")," i "," ")," dels "," ")," de les "," ")," les "," ")," els "," ")," De Los "," ")," De Las "," ")," De La "," ")," Del "," ")," De "," ")," El "," ")," La "," ")," Los "," ")," Las "," ")," Y "," ")," I "," ")," Dels "," ")," De Les "," ")," Les "," ")," Els "," "),C21),C21)My question is: is there a way to combine these two? I'd like to create a formula that sorts cells in a range with the ability to remove prepositions and articles during the sorting process. I tried with a LET function, but I can't figure out the way to blend them together. Here is a sample of what the sorted list looks like:
| Avaluació 2022-23 PROVES.xlsx | ||||||
|---|---|---|---|---|---|---|
| C | D | E | F | |||
| 4 | Jorge Guzmán Pedraza | Aura López Alcázar | Delete | |||
| 5 | David Tejero García | Carlos de la Torre Ramírez | ||||
| 6 | Tamara Bas Moreno | David Tejero García | ||||
| 7 | Miguel de Ureña Pérez | Fátima Andrade Bayarri | ||||
| 8 | Mónica Estadellas Vaca | Francesco Schettino | ||||
| 9 | Lena García Alegre | Gabriela López-Moreno del Pino | ||||
| 10 | Francesco Schettino | Javier Alarcón Aznar | ||||
| 11 | Marta Arenas Cabrerizo | Javier Fernández Parra | ||||
| 12 | Aura López Alcázar | Jorge Guzmán Pedraza | ||||
| 13 | Javier Alarcón Aznar | Juan Antonio García García | ||||
| 14 | Kamohelo Hraira | Kamohelo Hraira | ||||
| 15 | Javier Fernández Parra | Lena García Alegre | ||||
| 16 | Fátima Andrade Bayarri | María de los Ángeles Ojeda de Morata | ||||
| 17 | Noemi Caravantes Fenollosa | Marta Arenas Cabrerizo | ||||
| 18 | Gabriela López-Moreno del Pino | Miguel de Ureña Pérez | ||||
| 19 | Carlos de la Torre Ramírez | Miguel Ureña Yubero | ||||
| 20 | Juan Antonio García García | Mónica Estadellas Vaca | ||||
| 21 | María de los Ángeles Ojeda de Morata | Noemi Caravantes Fenollosa | ||||
| 22 | Miguel Ureña Yubero | Tamara Bas Moreno | ||||
| 23 | ||||||
| 24 | ||||||
| 25 | ||||||
Ordenadora | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4:D25 | D4 | =IFERROR(INDEX($C$4:$C$1000,MATCH(SMALL(NOT($C$4:$C$1000="")*IF(ISNUMBER($C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000)+SUM(--ISNUMBER($C$4:$C$1000))),ROWS($A$2:A2)+SUM(--ISBLANK($C$4:$C$1000))),NOT($C$4:$C$1000="")*IF(ISNUMBER($C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000),COUNTIF($C$4:$C$1000,"<="&$C$4:$C$1000)+SUM(--ISNUMBER($C$4:$C$1000))),0)),"") |
Thanks very much, guys!
