0

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

4
  • Most of March columns are empty, the highest consumption formula doesn't match the formula in the sample sheet. – Rubén Apr 6 at 20:50
  • I populated the rest of march's columns and fixed the formula – abbood Apr 7 at 5:18
  • Filling the spreadsheet with so many randbetween was not nice (makes the spreadsheet very slow). – Rubén Apr 8 at 3:02
  • You seem hard to impress ruben – abbood Apr 8 at 4:51
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.

10
  • cool, I updated my question with a more representative sample sheet btw. I'll take a look at your answer all the same – abbood Apr 5 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 – abbood Apr 5 at 13:14
  • I updated the sheet and have global permissions – abbood Apr 5 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 – abbood Apr 5 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? – abbood Apr 5 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.