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

Question

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

the issue is not "concatenation within vlookup" but 17500 vlookups in one go - it's too much hence that slowdown in performance — player0, Aug 23 '19 at 11:37

player0 · Accepted Answer · 2019-08-23T11:43:27.417

3

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

edited Aug 23 '19 at 11:43

answered Aug 23 '19 at 11:32

player0

124,011
12
67
124

1

I wasn't sure for the sorting, thanks for this, that's cleaner indeed. And since number of column is static, concatenating straight in the query data is also faster and removes the need for the `substitute()` operation. Smart. You are definitely the king of `query()` :-) – Benoît Wéry Aug 25 '19 at 07:40

score 2 · Answer 2 · answered Aug 23 '19 at 11:17

2

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

answered Aug 23 '19 at 11:17

Benoît Wéry

862
1
6
8

1

indeed nice one – player0 Aug 23 '19 at 11:22
Great formula indeed. I was looking more into 'filling' the grid than building it entirely. Now I need to check if I can still have control on the 'indices' row and column. – PopGoesTheWza Aug 23 '19 at 11:37
Since @player0 and your own formula only needs the dataset as input, it can easily be filtered if need be: `=ARRAYFORMULA( QUERY( ARRAYFORMULA(FILTER({data!A2:A, data!B2:B, data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F},MATCH(data!A2:A,C1:1,0),MATCH(data!B2:B,A3:A,0))), "select Col2,max(Col3) where Col2 is not null group by Col2 pivot Col1",0 ) )` Many thanks to both of you for this awesome and elegant solution. – PopGoesTheWza Aug 23 '19 at 13:00

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

2 Answers2