CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
SQLLER
SQLLER

Reputation: 31

Google Query not filling up data automatically

I am trying to get data from sheet1 of google sheet to sheet2 of same google sheet based on formula where Sheet2 A1:A equals to sheet1 A1:A and D = 1.

Lets say google sheet workbook name 'Test _query'

Data in Sheet1

Disease,state, link, rank
Covid, NC, url1,1
Covid, NY, url2,2
Covid, WDC, url2,3
polio, NY, url5,1
Small pox, CL, url1, 1
Small pox, CN, url2, 2

Data in Sheet2 (Output I want)

Disease,Disease 1,state 1, link 1, rank 1,Disease 2,state 2, link 2, rank 2,Disease 3,state 3, link 3, rank 3
Covid,Covid, NC, url1,1,Covid, NY, url2,2,Covid, WDC, url2,3
polio,polio, NY, url5,1
Small pox,Small pox, CL, url1, 1,Small pox, CN, url2, 2

in sheet2 cells below Disease 1,2,3 I used formula

=ArrayFormula(UNIQUE(QUERY('Sheet1'!A:D, "SELECT A,B,C,D WHERE D = 1 AND A = '"&A1:A&"'", 0)))

Output after using the above formula

Disease,Disease 1,state 1, link 1, rank 1,Disease 2,state 2, link 2, rank 2,Disease 3,state 3, link 3, rank 3
Covid,Covid, NC, url1,1,Covid, NY, url2,2,Covid, WDC, url2,3
Polio,
Small pox,

To get results for 2nd & 3rd rows if I drag that formula then its gives result but I want to automatically fill up rest of the rows.

Upvotes: 0

Views: 46

Answers (1)

Martín
Martín

Reputation: 10187

You can try with BYROW to repeat the process in the whole column:

=BYROW(A2:A, LAMBDA(each,UNIQUE(QUERY('Sheet1'!A:D, "SELECT A,B,C,D WHERE D = 1 AND A = '"&each&"'", 0))))

Upvotes: 0

Related Questions