Hi guys!
I have the following formula:
=IFERROR(IF($G$6="Delete",LET(list,REDUCE(C4:C1000,'Test1'!$A$200:$ZZ$200,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "))),ord,SORT(list),FILTER(ord,ord<>"")),LET(ord,SORT(C4:C1000),FILTER(ord,ord<>""))),"")
This formula sorts full names. However, if G6 has "Delete", it deletes all prepositions from the full names (in the cases of "de la Puebla", "della Casagrande" or "van der merwe"). All possible prepositions are located in Test1'!$A$200:$ZZ$200. After deleting them, it sorts the list without prepositions.
I need to create an additional parameter that says "Ignore", with a way to sort the list displaying the full names but not taking prepositions into account. Let me give you an example:
Pablo Jiménez and Pablo de la Puebla would be sorted as "Pablo de la Puebla" and "Pablo Jiménez". I need it to sort it as "Pablo Jiménez" and "Pablo de la Puebla". That is, sort without prepositions and articles but include them in the list to be displayed. Is this possible?
Thanks so much!
I have the following formula:
=IFERROR(IF($G$6="Delete",LET(list,REDUCE(C4:C1000,'Test1'!$A$200:$ZZ$200,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "))),ord,SORT(list),FILTER(ord,ord<>"")),LET(ord,SORT(C4:C1000),FILTER(ord,ord<>""))),"")
This formula sorts full names. However, if G6 has "Delete", it deletes all prepositions from the full names (in the cases of "de la Puebla", "della Casagrande" or "van der merwe"). All possible prepositions are located in Test1'!$A$200:$ZZ$200. After deleting them, it sorts the list without prepositions.
I need to create an additional parameter that says "Ignore", with a way to sort the list displaying the full names but not taking prepositions into account. Let me give you an example:
Pablo Jiménez and Pablo de la Puebla would be sorted as "Pablo de la Puebla" and "Pablo Jiménez". I need it to sort it as "Pablo Jiménez" and "Pablo de la Puebla". That is, sort without prepositions and articles but include them in the list to be displayed. Is this possible?
Thanks so much!