How to extract unique values without using unique function

[Pasadu]

Dear Sir, please could you help me with another formula to extrac unique values without using the unique function?

 

[Hui]

Pasadu

Please next time, attach a sample file, It takes time to make a file to test, which we can use on other problems

Assuming your data is in A1:
in B2: =IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

Copy down

 

[Pasadu]

Thank You, i have tried this but i get a different answer. I have attached the file please. Please note that the list will continue to be updated, therefore the unique list should continuously update itself. The order numbers are repeated because it is based on the number of containers though. Thank You.

Pasadu

Please next time, attach a sample file, It takes time to make a file to test, which we can use on other problems

Assuming your data is in A1:
in B2: =IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

Copy down

 

[Hui]

B2: =IFERROR(INDEX($A$2:$A$1000, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$1000), 0, 0), 0)), "")
copy down

 

[pecoflyer]

This topic seems related to https://chandoo.org/forum/threads/a...ells-whenever-list-is-uniquely-updated.47941/

 

[AlanSidman]

An alternative solution is to use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"SHIPPING ORDER NUMBER"}, {{"Data", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Data"})
in
    #"Removed Columns"