Search code examples
google-sheetsgoogle-sheets-formula

IMPORTRANGE: how to Pivot, Group, and Aggregate by two types of animals

I have an IMPORTRANGE(...) that yields the following table:

Adoption Date Take-Home Date Name Dogs Puppies Cats Kittens Chickens Chicks ...
01/25/2023 01/26/2023 Cody 3 0 2 1 30 5 ...
02/24/2024 02/29/2024 Rob 0 2 0 4 0 0 ...
01/29/2024 01/29/2024 Rob 1 0 2 4 2 10 ...
01/29/2024 01/29/2024 Rob 2 1 0 1 5 3 ...
... ... ... ... ... ... ... ... ... ...

I would like to wrap that function in something like a QUERY() with a GROUPBY or a PIVOT() or some combination of TRANSPOSE() and SUM() to yield the following result:

Adoption Date Take-Home Date Name Animal # Adults # Young
01/25/2023 01/26/2023 Cody Canine 3 0
01/25/2023 01/26/2023 Cody Feline 2 1
01/25/2023 01/26/2023 Cody Bird 30 5
02/24/2024 02/29/2024 Rob Canine 0 2
02/24/2024 02/29/2024 Rob Feline 0 4
01/29/2024 01/29/2024 Rob Canine 3 1
01/29/2024 01/29/2024 Rob Feline 2 5
01/29/2024 01/29/2024 Rob Bird 7 13
... ... ... ... ... ...

Note that there is no row for Rob's Birds, since those values are all zero.

In essence, I need to take all unique combinations of Adoption Date, Take-Home Date, Name, and (pivoted) animal species, and aggregate the sum of adults and the sum of young. I'm sure this is possible in one formula, but can't wrap my mind around the grouping, aggregation, and pivot as a function of a query SQL statement. Thanks for any help.

Solution

  • Here's one approach you may test out:

    =let(Σ,A2:D, Λ,lambda(x,wrapcols(x,rows(Σ),x)),
     query(vstack({Σ,Λ("Canine"),E2:F},
                  {Σ,Λ("Feline"),G2:H},
                  {Σ,Λ("Bird"),  I2:J}),
 "select Col1,Col2,Col3,Col4,Col5,sum(Col6),sum(Col7) where Col6>0 OR Col7>0 group by Col1,Col2,Col3,Col4,Col5 label sum(Col6) '',sum(Col7) ''"))

    enter image description here

    • If you need to add, let's say, one more species (horse, baby horse) in columns K,L; then the total range (assigned to shortcut Σ) would be A2:L && within the vstack you need to add the additional condition as such {Σ,Λ("Equine"),K2:L}
    • Last Name is added as Column_D here for representation

    This formula version embeds & processes your importrange data directly.

    =let(Σ,importrange("Sheet_ID","data!A2:J"), Γ,choosecols(Σ,1,2,3,4), Λ,lambda(x,wrapcols(x,rows(Σ),x)),
     query(vstack({Γ,Λ("Canine"),choosecols(Σ,5,6)},
                  {Γ,Λ("Feline"),choosecols(Σ,7,8)},
                  {Γ,Λ("Bird"),  choosecols(Σ,9,10)}),
 "select Col1,Col2,Col3,Col4,Col5,sum(Col6),sum(Col7) where Col6>0 OR Col7>0 group by Col1,Col2,Col3,Col4,Col5 label sum(Col6) '',sum(Col7) ''"))

    enter image description here