How to get min() max() sequentially from a single column?-CodePudding

Hi Stackoverflow Community,

This is my first post, apologies ahead if I haven't structured my question better, I'll try to improve on it later on.

I have a 2 excel columns with street address numbers and street address, none of which are unique hence using vlookup/index match is tricky.

I am trying to populate another column with the minimum and maximum value of the street address numbers using TEXTJOIN and it works, however I need the min/max for each specific street address group, there are close to 1 million lines of data.

For example, min=1, max=13, on florence st, min=3, max=53, on gibson st

enter image description here

CodePudding user response：

Is such a formula helpful for you:

=MINIFS(B$2:B$10,A$2:A$10,"Ramsay street")

As you see, I take the minimum value of the "B" column, based on a criterion on the "A" column.

Hereby a screenshot of an example:

CodePudding user response：

If you are on Excel 365 - current channel you can use this formula:

=LET(streetnamesUnique,UNIQUE(data[StreetName]),
minNumber,BYROW(streetnamesUnique,LAMBDA(s, MINIFS(data[Number],data[StreetName],s))),
maxNumber,BYROW(streetnamesUnique,LAMBDA(s, MAXIFS(data[Number],data[StreetName],s))),
HSTACK(streetnamesUnique,minNumber,maxNumber))

If on Excel 365 semi annual channel:

=LET(streetnamesUnique,UNIQUE(data[StreetName]),
minNumber,BYROW(streetnamesUnique,LAMBDA(s, MINIFS(data[Number],data[StreetName],s))),
maxNumber,BYROW(streetnamesUnique,LAMBDA(s, MAXIFS(data[Number],data[StreetName],s))),
MAKEARRAY(ROWS(streetnamesUnique),3,LAMBDA(r,c,
   IF(c=1,INDEX(streetnamesUnique,r),
   IF(c=2,INDEX(minNumber,r),
   INDEX(maxNumber,r))))))

Both formulas first retrieve the unique streetnames - then retrieves, per each streetname min and max number.

In the end the new range is built from these values - either by HSTACK or MAKEARRAY.