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

1
votes

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:

  • A, product reference
  • B: customer
  • C to F, KPI1 to KPI4

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
  )
)
2
google-sheetspivotgoogle-sheets-formulaarray-formulasgoogle-sheets-query
@player0 here: <docs.google.com/spreadsheets/d/…> – PopGoesTheWza
the issue is not "concatenation within vlookup" but 17500 vlookups in one go - it's too much hence that slowdown in performance – player0

2 Answers

3
votes

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

2
votes

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))