Forum Discussion

ashtar123's avatar
ashtar123
Copper Contributor
Feb 11, 2023

Let Excel recommend the largest current value among a series of data? Please help!!!

Hi Excel experts, Would you be able to kindly help with setting up a formula or VBA (whichever is simple) to catch the largest current value of a series of values? Please see attached work file...
excel
Formulas and Functions
Macros and VBA
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 11, 2023

ashtar123 

How to make a meal of it in 365!

The formula goes in cell A2 and spills to form the result table.

= LargestCodesλ(uniqueID)

The Lambda function is defined to be

= LET(
      productCode,  LEFT(uniqueID, 10),
      locationCode, VALUE(RIGHT(uniqueID, 6)),
      distinct,     UNIQUE(productCode),
      finalCode,    MAP(distinct, LAMBDA(d,
          LET(
              filteredLocation, FILTER(locationCode, productCode = d),
              d & TEXT(MAX(filteredLocation), "000000")
          )
      )),
      MID(finalCode, {1, 4, 8, 11, 14}, {3, 4, 3, 3, 3})
  )

[Lists distinct products with their largest Aisle and Tray codes]

 

Resources