Formula to Extract Highest Unique from 2 Columns

[TheCobbler]

Hi,

I'm looking for a fomula to help me extract the highest unqiue value in a 2 column list.
I need a unique value from column B but it has to be the highest relevant value in column A.

Example below of the desired result. Columns A & B to become C & D.

Thanks as always for any help and guidance,
Cobb

A	B	C	D
1.55	4.74	1.55	4.74
1.60	5.49	2.30	5.49
1.65	5.49	2.50	5.74
1.70	5.49
1.75	5.49
1.80	5.49
1.85	5.49
1.90	5.49
1.95	5.49
2.00	5.49
2.05	5.49
2.10	5.49
2.15	5.49
2.20	5.49
2.25	5.49
2.30	5.49
2.35	5.74
2.40	5.74
2.45	5.74
2.50	5.74

 

[Peter_SSs]

Like this?

23 08 15.xlsm
	A	B	C	D
1	A	B	C	D
2	1.55	4.74	1.55	4.74
3	1.6	5.49	2.3	5.49
4	1.65	5.49	2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	1.9	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
TheCobbler

Cell Formulas
Range	Formula
C2:D4	C2	=LET(u,UNIQUE(B2:B21),HSTACK(BYROW(u,LAMBDA(r,TAKE(SORT(FILTER(A2:A21,B2:B21=r)),-1))),u))
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	A	B	C	D
2	1.55	4.74	1.55	4.74
3	1.6	5.49	2.3	5.49
4	1.65	5.49	2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	1.9	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
22
Data

Cell Formulas
Range	Formula
C2:D4	C2	=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),HSTACK(XLOOKUP(u,B2:B100,A2:A100,,0,-1),u))
Dynamic array formulas.

 

[TheCobbler]

Hi both! Thanks for the speedy response. Both perfect. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Both perfect.

Note that they return different results if it was possible that column A is not always increasing.

23 08 15.xlsm
	A	B	C	D	E	F	G
1	A	B	C	D
2	1.55	4.74	1.55	4.74		1.55	4.74
3	1.6	5.49	20	5.49		2.3	5.49
4	1.65	5.49	2.5	5.74		2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	20	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
TheCobbler (2)

Cell Formulas
Range	Formula
C2:D4	C2	=LET(u,UNIQUE(B2:B21),HSTACK(BYROW(u,LAMBDA(r,TAKE(SORT(FILTER(A2:A21,B2:B21=r)),-1))),u))
F2:G4	F2	=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),HSTACK(XLOOKUP(u,B2:B100,A2:A100,,0,-1),u))
Dynamic array formulas.

.. and if column A is always increasing & column B grouped as per your sample then you would only need this

23 08 15.xlsm
	A	B	C	D
1	A	B	C	D
2	1.55	4.74	1.55	4.74
3	1.6	5.49	2.3	5.49
4	1.65	5.49	2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	1.9	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
22
TheCobbler

Cell Formulas
Range	Formula
C2:D4	C2	=FILTER(A2:B21,B2:B21<>B3:B22)
Dynamic array formulas.

 

[JvdV]

Book1
	A	B	C	D	E
1	1,55	4,74		1,55	4,74
2	1,6	5,49		2,3	5,49
3	1,65	5,49		2,5	5,74
4	1,7	5,49
5	1,75	5,49
6	1,8	5,49
7	1,85	5,49
8	1,9	5,49
9	1,95	5,49
10	2	5,49
11	2,05	5,49
12	2,1	5,49
13	2,15	5,49
14	2,2	5,49
15	2,25	5,49
16	2,3	5,49
17	2,35	5,74
18	2,4	5,74
19	2,45	5,74
20	2,5	5,74
Sheet1

Cell Formulas
Range	Formula
D1:E3	D1	=LET(x,UNIQUE(B1:B20),HSTACK(MAXIFS(A1:A20,B1:B20,x),x))
Dynamic array formulas.

 

[TheCobbler]

Note that they return different results if it was possible that column A is not always increasing.

23 08 15.xlsm
	A	B	C	D	E	F	G
1	A	B	C	D
2	1.55	4.74	1.55	4.74		1.55	4.74
3	1.6	5.49	20	5.49		2.3	5.49
4	1.65	5.49	2.5	5.74		2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	20	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
TheCobbler (2)

Cell Formulas
Range	Formula
C2:D4	C2	=LET(u,UNIQUE(B2:B21),HSTACK(BYROW(u,LAMBDA(r,TAKE(SORT(FILTER(A2:A21,B2:B21=r)),-1))),u))
F2:G4	F2	=LET(u,UNIQUE(FILTER(B2:B100,B2:B100<>"")),HSTACK(XLOOKUP(u,B2:B100,A2:A100,,0,-1),u))
Dynamic array formulas.

.. and if column A is always increasing & column B grouped as per your sample then you would only need this

23 08 15.xlsm
	A	B	C	D
1	A	B	C	D
2	1.55	4.74	1.55	4.74
3	1.6	5.49	2.3	5.49
4	1.65	5.49	2.5	5.74
5	1.7	5.49
6	1.75	5.49
7	1.8	5.49
8	1.85	5.49
9	1.9	5.49
10	1.95	5.49
11	2	5.49
12	2.05	5.49
13	2.1	5.49
14	2.15	5.49
15	2.2	5.49
16	2.25	5.49
17	2.3	5.49
18	2.35	5.74
19	2.4	5.74
20	2.45	5.74
21	2.5	5.74
22
TheCobbler

Cell Formulas
Range	Formula
C2:D4	C2	=FILTER(A2:B21,B2:B21<>B3:B22)
Dynamic array formulas.

Thanks for highlighting this. They will be 'if column A is always increasing & column B grouped as per your sample then you would only need this'. I've given all these a test and it's very interesting to see the differences. I've currently implemented your shortened filter but there's a lot of new functions for me to digest here! Thank you again for sharing your knowledge.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.