CODEWITHSUNDEEP
google-sheetspivotgoogle-sheets-formulaarray-formulasgoogle-sheets-query
PopGoesTheWza
PopGoesTheWza

Reputation: 618

Formula to fill a two dimensional grid with computed value from a table

Here is a problem to solve with a Google Sheets formula. I have a big table (sheet “data” with headers on the first row) with those columns:

On another sheet, a grid of product references (A2:A) by customer (B1:1). Now I need to fill each cell from the grid with the concatenation of KPIs (data!C24&"|"&data!D24&"|"&data!E24&"|"&data!F24) Could you workout a single formula to fill all the cells?

Here is a sample spreadsheet with the data and grid sheet: https://docs.google.com/spreadsheets/d/1iA_kw4kKw99Qk69X4tST9U-QN2SeG2EN3KEeyG6AtHs/edit?usp=sharing

I have worked out a formula which does the job, though with very poor performance on large dataset:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      $B3:$B&"|"&C$2:$2,
      ARRAYFORMULA(
        {data!A2:A&"|"&data!B2:B,data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F}
      ),2,0
    ),""
  )
)

Solution

Use an ArrayFormula on a Query with Pivot:

=ARRAYFORMULA(
  QUERY(
    {data!A2:A, data!B2:B, data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F},
    "select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2",0
  )
)

Upvotes: 2

Views: 1625

Answers (2)

player0
player0

Reputation: 1

a shorter version of previous answer (no need for pre-sorting coz pivot will sort it on its own):

=ARRAYFORMULA(QUERY(
 {data!A2:A, data!B2:B, data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F},
 "select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2", 0))

0

Upvotes: 3

Benoît Wéry
Benoît Wéry

Reputation: 862

Try this on the first cell of your grid:

=ArrayFormula(query(sort({data!A:A,data!B:B,transpose(substitute(query(transpose(data!C:F),,4)," ","|"))},1,true,2,true),"select Col1, max(Col3) where Col1 is not null group by Col1 pivot Col2",0))

Upvotes: 2

Related Questions