Extracting unique values from a range

[Jasesair]

I am want to use something similar to the below formula to extract unique values from a range (AB14:BZ33), however this formula will only work when I change the range to simply being a row. The formula is also removing the blanks, which is ideal. To make this one work, I need to Ctrl-Shift-Enter, then drag down as needed.

Can anyone amend or offer a solution so this works across a range rather than just a row?

Many thanks for any assistance

=IFERROR(INDEX($AB$14:$BZ$33,MATCH(0,IF(ISBLANK($AB$14:$BZ$33),1,COUNTIF($B$53:B53,$AB$14:$BZ$33)),0)),"")

 

[earthworm]

Please share sample data

 

[Jasesair]

Sorry, I didn't think anyone would be interested in a sample. It's just names randomly in each cell of the range...lots of blank cells and others with names. The formula will be going in cell b54 and be dragged down as far as needed.

 

[earthworm]

Suppose your data starts from Cell A1 , do you want to extract unique in cell B1 ?

 

[JEC]

Not sure what you want but you could run this macro

Sub jec()
 With CreateObject("scripting.dictionary")
   For Each it In Range("AB14:BZ33")
     c00 = .Item(it.Value)
   Next
  Range("B54", Range("B" & Rows.Count).End(xlUp)).ClearContents
  Range("B54").Resize(.Count) = Application.Transpose(.keys)
 End With
End Sub

 

[Peter_SSs]

.. formula to extract unique values from a range ..

See if post 5 here helps: Unique in Range

 

[Peter_SSs]

See if post 5 here helps:

I did not have time when I posted that link yesterday, but adapting it to your data range and (I gather) your formula location ..
With your version you may need Ctrl+Shift+Enter?

Jasesair.xlsm
	B																									AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
14																											Plum								Peach
15																											Melon	Melon	Melon	Melon
16
17																													Plum	Watermelon		Apple			Peach		Peach
18
19
20																											Melon	Kiwifruit					Blackberry
21																												Apple
22																												Apple	Peach				Raspberry
23																												Apple
24
25																											Raspberry						Blueberry		Cranberry		Banana
26
33
52
53	Plum
54	Peach
55	Melon
56	Watermelon
57	Apple
58	Kiwifruit
59	Blackberry
60	Raspberry
61	Blueberry
62	Cranberry
63	Banana
64
65
Sheet1

Cell Formulas
Range	Formula
B53:B65	B53	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(AB$14:BZ$33)*10^6+COLUMN(AB$14:BZ$33))/((AB$14:BZ$33<>"")*(ISNA(MATCH(AB$14:BZ$33,B$52:B52,0)))),1),"R000000C000000"),0),"")

 

[Jasesair]

Peter, that worked perfectly. Thanks so much!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.