Get Unique list from column with formule

[Monty]

Hello Experts.

Please help me with Getting unique list from column with formula..

i do not want to use Array formula.. and wanted dynamic last row.

Please find attatched for your reference.


Country Output
India India
USA USA
London London
India
USA
London
India
USA
London
India
USA
London

 

[Chihiro]

In B2:
=IF(COUNTIF($A$2:A2,A2)=1,A2,"") Copy down.

To make it dynamic, convert range to table.

 

[Monty]

Hello Chihiro.

Thanks for your quick update.

But this formula works but leaves blanks.

Qucik snapshot for you.


Country Output
India India
USA USA
London London
India
USA
hello hello
India
USA
London
India
USA
London

 

[Chihiro]

If you need sorted list without sorting original range... I'd go with array formula.

A question, why aversion to array formula?

 

[Deepak]

=LOOKUP(0,COUNTIF($B$1:B1,$A$2:$A$10),$A$2:$A$10)

for dynamic range - create named range.

other alternatives are pivot and vba barring frontend array.

 

[Monty]

Thanks Deepak.

out put is not coming as expecting...can you help.

A k
b s
c c
d b
a a
b #N/A
c #N/A
s #N/A
k #N/A

 

[Deepak]

Countif doesn't care about case whereas lookup takes charge from right/bottom.

 

[Monty]

Yes deepak.

That's the problem..Any other solution...It works perfectly with Arrary formules but need dynamic range for that..if we do not have any other option other then array.

Here is my formule which am using but not dynamic.

ARRAY FORMULA

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))




Thanks

 

[Deepak]

Why not using a named range for ($A$2:$A$20) with the array.

I don't think without array it's possible but will check with pc soon.

 

[Chihiro]

Yep, dynamic named range is the way to go.

Named range formula (named "lstCountry")
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Then formula in B2:
=IFERROR(INDEX(lstCountry, MATCH(0, COUNTIF($B$1:B1, lstCountry), 0)),"")

Confirmed as array (CSE)

 

[Monty]

Yes deepak.

But when we increase that range ($A$2:$A$20) to A21 it does't work....as this formula expects range should be same in A column too..

 

[Deepak]

Chihiro hits the ball before u thought.

 

[Monty]

Chihiro

This works perfectly as discussed...but this follows with zero always at the end.

 

[Monty]

Yes Chihiro had hit the ball...we are almost there...but still we can live with this. Hattsoff..

 

[bosco_yip]

This works perfectly as discussed...but this follows with zero always at the end.
View attachment 37497

Try to add &"" at the CSE-formula end to remove zero.

=IFERROR(INDEX(lstCountry,MATCH(0,COUNTIF($B$1:B1,lstCountry),0)),"")&""

Regards

 

[Chihiro]

...but this follows with zero always at the end.

Odd. I can't replicate your issue on my end. Double check your dynamic named range formula.


FYI - You should have column header in A1, or formula may not return last match.

 

[rahulshewale1]

hii chihiro sir,

Could you please explain below formula how work?

Then formula in B2:
=IFERROR(INDEX(lstCountry, MATCH(0, COUNTIF($B$1:B1, lstCountry), 0)),"")


Thanks
rahul shewale

 

[Chihiro]

Here. You can find good explanation of how the formula works in the link.
https://www.mrexcel.com/forum/excel-questions/504695-explain-logic-match-countif-array-{}.html