Formula to pick up max value and apply that max record values to other recordswith same ID

Hi All,

I'm need of a generalised formula for many such attributes with many records

I have two records, with three columns
A1: Base B1: Attribute 1 C1: Attribute 2
A2: 766786 B2: 2 C2: 3
A3:766786 B3: 3 C3: 2

I want a fourth and fifth column with the following logic:

1. Check for the data in row 2 and 3 for each of the Attribute 1 and Attribute 2, i.e. B2 against B3 and C2 against C3
2. If the data is same, then for both the Base records pick one of the record from the two and fourth column will have the Attribute 1 data and fifth column will have Attribute 2 data
3. If the data is not same, then check for Attribute 1 column and pick the max of the value and assign the associated max value record to column four and five, ie. as per the above example fourth column will have 3 and fifth column will have 2 as this is the max value associated record
4. IF the data is not same and the Attribute 1 data is same, then check for Attribute 2 column and pick the max of the value and assign the associated max value record to column four and five
5. Similarly this formula should carry out for any number of attributes and perform the checks as explained in 2, 3 and 4

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This formula returns the requested output, but does not repeat:

Please try

Formula:

Please Login or Register  to view this content.

Is this also sufficient? Or should the output be repeated for every time the base also repeats?

LET(b,A2:A17,...): Name the range A2:A17 with the bases b

FILTER(b,b): Ignore empty values

u,UNIQUE(FILTER(b,b)): Determine the unique values ​​of the bases and call them u

REDUCE("",u,LAMBDA(i,v: Perform a certain operation per unique base and an initial value and after each operation replace the initial value with the result of that operation.
The initial value is an empty value the first time and is called i in the formula with the operation, and the base is called v in the formula with the operation.

VSTACK(i,TAKE(SORT(FILTER(B2:D17,b=v),{1,2,3}),-1)):This is the third parameter of the LAMBDA function and contains the operation to be repeated for each unique base:

FILTER(B2:D17,b=v): Select all rows with attributes belonging to a particular base
SORT(...,{1,2,3}): Sort them in ascending order by Attribute 1, within that by attribute 2, and within that by attribute 3.
TAKE(...,-1): And take the last (bottum) row of that
VSTACK(i,....): Place that row below the initial value and set the result as the new initial value

Then repeat the steps above for each unique base

DROP (....,1): Remove the first row from the final result so far. That's the first initial value with the empty cell I started with.

HSTACK(u,....): Place the column with the unique bases and the final result obtained from the REDUCE LAMBDA function next to each other.

hi there, after check the consult I allow to propose another possible solution:

Please Login or Register  to view this content.

I hope it results useful.

Test (Solution).xlsx

Very nice ExeLogan.

Here's another formula that repeats results for every occurrence of a base.

This is a copy down formula: Try in A2 and copy down:

Formula:

Please Login or Register  to view this content.

Yes your formula is neater ExceLogan. I will study your formula and learn from it. Thanks.

You also bypassed the SORT nicely, but I was a bit lazy.

Tomorrow I will think about passing the SORT in a different way.