How might one generate a distinct list that keeps a static order?

[mwscarponi]

I am currently generating a distinct list from a list with duplicates using:

=IFERROR(INDEX(C15:C50, MATCH(0, INDEX(COUNTIF(D50:D50, C15:C50), 0, 0), 0)), "")

However, when the source list changes, the entries of the distinct list will (potentially) reorder. This is messing up another purpose of the worksheet. Is there a way to "lock in" the cells of the distinct list as they propagate?


EXAMPLE:

If my source list is:

Orange
Red
Orange


My distinct list is:

Orange
Red

But if the source list changes to:

Blue
Blue
Orange
Red
Orange

The distinct list will be:

Blue
Orange
Red

But this changes Orange and Red from the 1st and 2nd cells in the list to the 2nd and 3rd. I need them to stay where they originate, and for the new entry (Blue) to be added at the end of the distinct list.

Thanks!

 

[sheetspread]

I'm not sure I understand what you're suggesting in that case.

Blanks can be included or not, either would be fine.
What would be the advanced filter option?

If your source data (including header) starts in A1, then define a name called source with the formula: =OFFSET(Sheet4!$A$2,0,0,COUNTA(Sheet4!$A:$A),1)

and you won't have to continuously update the formula above. Or just highlight the range, and under advanced filter check unique records only to bypass the formula method altogether. Then the only manual tasks will be pasting each new source directly below the other.

 

[mwscarponi]

Then the only manual tasks will be pasting each new source directly below the other.

Yeah, my goal was to be able to keep the source list in it's slotted order, where a new entry could go in at any blank cell (above or below existing entries in the list). That's precisely why the distinct list keeps changing in order. I'm pretty sure I wouldn't have to change anything as the sheet currently stands if the source list was created top down.