0

I originally posted because I couldn't work out why I was getting Array Literal Error when I have same amount of columns in each formula. However, I worked out what throws it off but not how to resolve it.

I am trying to import two ranges from the same source sheet to one destination sheet to create a database. There are instances where there is more than one name (1st player and 2nd player) on the same row in source sheet, which is why it has been complicated. The formula is:

={QUERY(IMPORTRANGE("Source sheet URL", "Form responses 8!C:AH"), "Select Col5, Col6, Col9, Col1, Col17, Col19, Col20, Col21, Col22, Col16, Col29, Col30 Where Col2 is null and Col5 is not null", 0);QUERY(IMPORTRANGE("Source sheet URL", "Form responses 8!C:AH"), "Select Col11, Col12, Col15, Col, Col17, Col19, Col20, Col21, Col22, Col16, Col31, Col32 Where Col2 is null and Col11 is not null", 0)}

I am trying to get a list of names and player details (a database) from a form responses sheet - one that will keep automatically updating when new responses come in.

Col5 is where player 1's data begins and Col11 is where player 2's data begins. They are siblings so they will share some columns.

It was working fine until I deleted my test player 2's that I entered because there were none in real life so far. I realised that's when I got Array Literal error. So do I need an instance in the formula for when there is a player 1 but no player 2? Do I need to add a third Importrange?

I tried:

=ARRAYFORMULA(IFERROR(QUERY({IFERROR( IMPORTRANGE("1datePnWQU7814T7XhFL0_LHzn479_kzcgZAdCJiSDsI", "Form responses 8!C:AH"), "Select Col5, Col6, Col9, Col1, Col17, Col19, Col20, Col21, Col22, Col16, Col29, Col30 Where Col2 is null and Col5 is not null", 0);IFERROR(IMPORTRANGE("1datePnWQU7814T7XhFL0_LHzn479_kzcgZAdCJiSDsI", "Form responses 8!C:AH"), "Select Col11, Col12, Col15, Col1, Col17, Col19, Col20, Col21, Col22, Col16, Col31, Col32 Where Col2 is null and Col11 is not null", 0)}

because I was told it is the same as another question but I am now getting #N/A. Not sure if I'm getting it right at all.

CinCout
  • 9,486
  • 12
  • 49
  • 67
Shelley Gichigi
  • 1
  • 1
  • in b/w Col15 and Col17 there seems to be just "Col". can you check on that – rockinfreakshow Dec 05 '22 at 14:45
  • Thanks, yeah, that's not the issue. I copied this from a notepad I was using but I checked and it's not like that on the sheet. – Shelley Gichigi Dec 05 '22 at 20:07
  • Try this: ={IFERROR(QUERY(IMPORTRANGE("1datePnWQU7814T7XhFL0_LHzn479_kzcgZAdCJiSDsI", "Form responses 8!C:AH"), "Select Col5, Col6, Col9, Col1, Col17, Col19, Col20, Col21, Col22, Col16, Col29, Col30 Where Col2 is null and Col5 is not null", 0),MAKEARRAY(1,12,LAMBDA(r,c,iferror(1/0))));IFERROR(QUERY(IMPORTRANGE("1datePnWQU7814T7XhFL0_LHzn479_kzcgZAdCJiSDsI", "Form responses 8!C:AH"), "Select Col11, Col12, Col15, Col1, Col17, Col19, Col20, Col21, Col22, Col16, Col31, Col32 Where Col2 is null and Col11 is not null", 0),MAKEARRAY(1,12,LAMBDA(r,c,iferror(1/0))))} – rockinfreakshow Dec 05 '22 at 20:49
  • I think it worked!! can't believe it! thank you so much... it's been 11 days!! Phew! I'm overjoyed because I'm doing this to help a friend who is sick with exhaustion and needs to simplify his work and reduce his time spent doing data input... so thank you again. – Shelley Gichigi Dec 06 '22 at 20:51
  • ... btw, what is LAMBDA?? – Shelley Gichigi Dec 06 '22 at 20:52

1 Answers1

0

run each importrange separately to allow access, then use your:

={QUERY(IMPORTRANGE("URL", "Form responses 8!C:AH"), 
 "select Col5,Col6,Col9,Col1,Col17,Col19,Col20,Col21,Col22,Col16,Col29,Col30 
  where Col2 is null and Col5 is not null", 0);
  QUERY(IMPORTRANGE("URL", "Form responses 8!C:AH"), 
 "select Col11,Col12,Col15,Col1,Col17,Col19,Col20,Col21,Col22,Col16,Col31,Col32 
  where Col2 is null and Col11 is not null", 0)}
player0
  • 124,011
  • 12
  • 67
  • 124