google-sheets - 根据公式传递的第三列数据唯一

Question

我有一个非常广泛的公式，可以在E2其中提供这些数据column E - F - G

我想知道如何在我的公式 ( E2) 中添加UNIQUE一种根据Column G.

我不会把公式放在问题中，因为它太大了，所以我会留下电子表格的链接，以防有必要对整个公式进行分析。

https://docs.google.com/spreadsheets/d/13FwHxEUz_dYzv4taarj_VzPI6yofYurwqUwSYIrHoN0/edit?usp=sharing

例如：

有一个价值：

2020/03/13 - PSTP - LINK/3031297/

还有价值：

2020/03/13 - 20:20 - LINK/3031297/

正确的是，在 之后UNIQUE，只保留这个值：

2020/03/13 - PSTP - LINK/3031297/

标签： google-sheetsgoogle-sheets-formula

Answer 1

As @MattKing hasn't posted the answer I will post it myself as Community Wiki.

So if you get all the imports sorted already you can just create a range using brackets {} and after that QUERY them and use an UNIQUE statement with ARRAYFORMULA.

In your particular case with all the imports you can just go ahead and do it like so:

=ARRAYFORMULA(VLOOKUP(UNIQUE(QUERY(TRIM(
{
IFERROR(
{
TEXT(IMPORTXML(D2,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D2,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D2,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D2,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D2,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D3,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D3,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D3,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D3,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D3,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D4,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D4,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D4,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D4,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D4,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D5,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D5,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D5,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D5,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D5,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D6,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D6,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D6,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D6,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D6,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
}),"select Col3 where Col3<>''")),QUERY(TRIM(
{
IFERROR(
{
TEXT(IMPORTXML(D2,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D2,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D2,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D2,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D2,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D3,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D3,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D3,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D3,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D3,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D4,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D4,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D4,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D4,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D4,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D5,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D5,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D5,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D5,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D5,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(
{
TEXT(IMPORTXML(D6,"//td[@class='date no-repetition' and ../td[@class='score-time status']]/span"),"yyyy/mm/dd"),
TEXT(SUBSTITUTE(IMPORTXML(D6,"//td[@class='score-time status']/a")," ",""),"hh:mm"),
IFERROR("https://int.soccerway.com"&LEFT(IMPORTXML(D6,"//td[@class='score-time status']/a/@href"),FIND("?ICID=",IMPORTXML(D6,"//td[@class='score-time status']/a/@href"))-1),"https://int.soccerway.com"&IMPORTXML(D6,"//td[@class='score-time status']/a/@href"))
}
,{"","",""})
;
IFERROR(FILTER(Sheet2!A2:C,Sheet2!A2:A<>""),{"","",""})
}),"Select Col3,Col1,Col2"),{2,3,1},0))

And change the range inside TRIM to whatever you want to adapt this code.

References

• ARRAYFORMULA
• VLOOKUP
• UNIQUE
• QUERY
• TRIM