How to filter for a unique value.

[helpneeded2]

I have Excel 365 and I just can't figure out how to use the UNIQUE / FILTER / SORT functions to get my desired result.

I have a table with 8 columns of data (A - H). the same value in Column B will appear multiple times. The reason Column B appears multiple times, is because data in Column C may contain different spellings of the same name.

I want a function that will allow me to sort the table so that all the columns of the table will appear, however it will be sorted on Column B, and will only show the values in Column B once, and it doesn't matter which value is matched in Column C.

Could someone please help me with this.

 

[RobP]

Hi,

sorry, my suggestion didn't work on more testing.. so I deleted it.

cheers
Rob

 

[Fluff]

How about

=LET(d,B2:B55,r,ROWS(d),SORT(FILTER(A2:H55,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(d=TOROW(d)),SEQUENCE(r,,,0))=1),2))

 

[RobP]

my test data is in (a2:H11) - and the rows you pull look good - however, for me its not sorting them for some reason on Col B ?

Rob

every_hour.xlsm
	A	B	C	D	E	F	G	H	I
1
2	a	b	c	d	e	f	g	h
3	e	b
4	d	qa
5	c	aq
6	f	b
7	g	w
8	h	sw
9	j	sw
10	k	x
11	l	y
12
13	c	aq	0	0	0	0	0	0
14	a	b	c	d	e	f	g	h
15	d	qa	0	0	0	0	0	0
16	h	sw	0	0	0	0	0	0
17	g	w	0	0	0	0	0	0
18	k	x	0	0	0	0	0	0
19	l	y	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
A13:H19	A13	=LET(d,B2:B11,r,ROWS(d),SORT(FILTER(A2:H11,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(d=TOROW(d)),SEQUENCE(r,,,0))=1),2))
Dynamic array formulas.

 

[Fluff]

That looks sorted to me.

 

[RobP]

haha,,,. sorry.. my eyes not working today .. apologies.

Rob