I have a big formula in my spreadsheet that imports data from internet pages, in the first column it imports the date, the second column the hour to kick off of the game and the third column the link to the match.
Some matches are canceled, causing column 2 where the times would appear, now "PSTP" will appear, as it is updated daily, in the records there may be a link with time but as the new one is appearing "PSTP", in this case I would like that neither the old nor the new appear in the list.
Current format:
Expected result:
I leave here the link to the spreadsheet so that it can be analyzed together with the existing formula because I will not publish it because it is too big:
https://docs.google.com/spreadsheets/d/1UyRqZPbZasqz6yIDSWTVCR_AuEm-cfoj8RHWfJ8Owio/edit?usp=sharing
try:
=ARRAYFORMULA(FILTER(A1:C, REGEXMATCH(VLOOKUP(A1:A&C1:C,
SORT({A1:A&C1:C, B1:B}, 1, 1, 2, 1), 2, 0), "FT|PSTP")))
or directly:
=ARRAYFORMULA(FILTER({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
}, REGEXMATCH(VLOOKUP(INDEX({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
},,1)&INDEX({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
},,3),
SORT({INDEX({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
},,1)&INDEX({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
},,3), INDEX({
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E2,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E2,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E2,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E2,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E2,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E2,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E3,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E3,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E3,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E3,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E3,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E3,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E4,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E4,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E4,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E4,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E4,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E4,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E5,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E5,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E5,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E5,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E5,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E5,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(E6,"//tr[@class='no-date-repetition-new' and ..//td[@class='score-time']]"),"yyyy/mm/dd"),
IF(IMPORTXML(E6,"//td[@class='score-time']")="PSTP","PSTP",TEXT(SUBSTITUTE(IMPORTXML(E6,"//div[@class='match-card match-hour']")," ",""),"hh:mm")),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(E6,"//td[@class='score-time']/a/@href"),FIND("?ICID=",IMPORTXML(E6,"//td[@class='score-time']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(E6,"//td[@class='score-time']/a/@href"))
}
,{"","",""})
},,2)}, 1, 1, 2, 1), 2, 0), "FT|PSTP")))