1

Background

I have a spreadsheet that displays data internet consumption by users that looks like this

userid feb1 feb2 feb3 . feb29 u1 100 34 23 . 4 u2 0 24 21 62 u3 300 25 5 1 u4 50 5 6 . . un 23 52 3 . 42

I already wrote a formula that lists the daily consumption of each user like so:

date daily consumption feb1 14,971 feb2 6,898 feb3 10,666 . . feb29 10,543

from the daily consumption I wrote a formula that gives me the highest amount consumed by a user on a daily basis so it looks like something like this

date daily consumption highest consumption feb1 14,971 7,523 feb2 6,898 3,422 feb3 10,666 5,411 . . feb29 10,543 6,234

(this is what the query is where March is the sheet that contains the raw data

query(transpose(QUERY(March!B3:C,"Select "&textjoin(",",true,ARRAYFORMULA("Max("& REGEXEXTRACT(address(1,column(March!B2:C),4,true,"March"),"March!(.*)\d") &")")) &"",0)),"select Col2")

Problem

What I'm trying to do now is find out on a daily basis the ID of the highest consumer like so:

date daily consumption highest consumption highest consumer id feb1 14,971 7,523 4779221 feb2 6,898 3,422 1047223 feb3 10,666 5,411 7505020 . . feb29 10,543 6,234 2994922

I have two ways of getting the highest consumer Id manually:

VLOOKUP method

ie

=vlookup(D2 ,{March!$I:$I,March!$B:$B},2,false) =vlookup(D3 ,{March!$J:$J,March!$B:$B},2,false) =vlookup(D4 ,{March!$K:$K,March!$B:$B},2,false) ..

query method

ie

=query({March!$I:$I,March!$B:$B},"select Col2 where Col1 contains "&trunc(D2,5)) =query({March!$J:$J,March!$B:$B},"select Col2 where Col1 contains "&trunc(D3,5)) =query({March!$K:$K,March!$B:$B},"select Col2 where Col1 contains "&trunc(D4,5))

However I'm struggling with writing a formula that makes either work in a single line. What I'm trying to do is something along these lines:

for vlookup option:

arrayformula(vlookup(D2:D3,indirect("March!$"& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d") &":$" & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d")), indirect("March!$B:$B"),2,false))

for query option:

arrayformula(query({indirect("March!$"& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d") &":$" & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d")), indirect("March!$B:$B")},"select *"))

How do I make this work?

why I need arrayformula rather than copy/pasting formulas across cells

the same sheet is very minimalist compared to the actual data I get from vendors. And this question is a about a single data analysis point I'm interested in - there are many more. Dragging formulas around will soon be cumbersome and not realistic. That's why I want to create arrayformulas instead.

Sample sheet

note: the formulas will be a bit different on this sample sheet (same structure, different cell references)

https://docs.google.com/spreadsheets/d/1OmVczb6KS8E6biDrtn8xSx1u1-urFjaFhY8lOVJdtGk/edit#gid=510221509

Improve this question
4
  • Most of March columns are empty, the highest consumption formula doesn't match the formula in the sample sheet. Commented Apr 6, 2021 at 20:50
  • I populated the rest of march's columns and fixed the formula Commented Apr 7, 2021 at 5:18
  • Filling the spreadsheet with so many randbetween was not nice (makes the spreadsheet very slow). Commented Apr 8, 2021 at 3:02
  • You seem hard to impress ruben Commented Apr 8, 2021 at 4:51

1 Answer 1

Reset to default
0

Try this:

=if( sum(B3:B), textjoin( ", ", true, filter($A3:$A, B3:B = max(B3:B)) ), iferror(1/0) )

To do the same with an array formula, try this:

=arrayformula( transpose( iferror( 1 / ( 1 / dmax( B3:Z, sequence(columns(B3:Z)), transpose(iferror(column(B3:Z) / 0)) ) ) ) ) )

To place these values in the 'Analysis' sheet, use this:

=arrayformula( iferror( hlookup( A2:A, { March!B2:2; March!B1:1 }, 2, false) ) )

Note that I had to replace the formula in A2 with a much simpler transpose(). The original formula is in B2.

See the 'Analysis' sheet in your sample spreadsheet.

Improve this answer
10
  • cool, I updated my question with a more representative sample sheet btw. I'll take a look at your answer all the same Commented Apr 5, 2021 at 13:10
  • your answer doesn't work b/c I need a single line formula, yours has a formula for each column which isn't scalable: =if( sum(C3:C), textjoin( ", ", true, filter($A3:$A, C3:C = max(C3:C)) ), iferror(1/0) ) and =if( sum(D3:D), textjoin( ", ", true, filter($A3:$A, D3:D = max(D3:D)) ), iferror(1/0) ) etc Commented Apr 5, 2021 at 13:14
  • I updated the sheet and have global permissions Commented Apr 5, 2021 at 15:38
  • The source data is the sheet "March".. I created a hand made column called "highest consumer id (static)".. I'm looking for a one line formula that we generate that column Commented Apr 5, 2021 at 18:16
  • why did you create a new "highest consumption" row in the "March" sheet.. instead of using the existing highest consumption column "ie Analysis!C1:C" in the Analysis sheet? Commented Apr 5, 2021 at 19:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.