CODEWITHSUNDEEP
arraysgoogle-sheetsarray-formulasgoogle-sheets-formula
Digital Farmer
Digital Farmer

Reputation: 2107

Join importxml & importhtml by braces

This formula is in A1:

=
{
{"LINK DA FOTO","LINK DO PERFIL"}
;
{"LINK DA FOTO","LINK DO PERFIL"}
;
ArrayFormula(SEERRO(PROCH(1,{1;IMPORTXML('Time Casa'!B12,"//table[@class='table squad sortable']//td[@class='photo']/a/img/@src | //table[@class='table squad sortable']//td[@class='name large-link']/a/@href")},(LIN($A$1:$A$52)+1)*2-TRANSPOR(sort(LIN($A$1:$A$2)+0,1,0)))))
}

This formula is in C1:

=
{TRANSPOR(IMPORTXML(
'Time Casa'!B12,"
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th/img/@title | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[6]/span | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[5] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[3] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[4] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[1] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[2]
"))
;
IMPORTHTML('Time Casa'!B12,"table","1")
}

Result: enter image description here

By uniting like this in Sheet2, it works perfectly, the result is exactly that of the image above.

=
{
Sheet3!A:B
,
Sheet3!C:S
}

But when joining via the same formula I did below, it gives error and says ↓

Function ARRAY_ROW parameter 2 has mismatched line length. Expected 54 and have: 39.

=
{
{
{"LINK DA FOTO","LINK DO PERFIL"}
;
{"LINK DA FOTO","LINK DO PERFIL"}
;
ArrayFormula(SEERRO(PROCH(1,{1;IMPORTXML('Time Casa'!B12,"//table[@class='table squad sortable']//td[@class='photo']/a/img/@src | //table[@class='table squad sortable']//td[@class='name large-link']/a/@href")},(LIN($A$1:$A$52)+1)*2-TRANSPOR(sort(LIN($A$1:$A$2)+0,1,0)))))
}
,
{TRANSPOR(IMPORTXML(
'Time Casa'!B12,"
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th/img/@title | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[6]/span | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[5] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[3] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[4] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[1] | 
//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[2]
"))
;
IMPORTHTML('Time Casa'!B12,"table","1")
}
}

I would like to know what I need to adjust so it can work, I tried to use =FILTER(X,X<>"") but the same error continued.
Link to Spreadsheet:
https://docs.google.com/spreadsheets/d/1DNhl5hf5ofST84nawfBF6kuzhn83UMldh1lS20VTJpA/edit?usp=sharing

Upvotes: 1

Views: 57

Answers (1)

player0
player0

Reputation: 1

try:

={{{"LINK DA FOTO", "LINK DO PERFIL"};
   {"LINK DA FOTO", "LINK DO PERFIL"};
 ARRAYFORMULA(QUERY(IFERROR(HLOOKUP(1, {1; IMPORTXML('Time Casa'!B12, 
 "//table[@class='table squad sortable']//td[@class='photo']/a/img/@src | 
  //table[@class='table squad sortable']//td[@class='name large-link']/a/@href")},
 (ROW($A$1:$A$52)+1)*2-TRANSPOSE(SORT(ROW($A$1:$A$2)+0, 1, 0)))), 
 "where Col1 is not null"))},
 {TRANSPOSE(IMPORTXML('Time Casa'!B12,
 "//*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th/img/@title | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[6]/span | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[5] | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[3] | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[4] | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[1] | 
  //*[@id='page_team_1_block_team_squad_3-table']/thead/tr/th[2]"));
 IMPORTHTML('Time Casa'!B12, "table", "1")}}

0

Upvotes: 1

Related Questions