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!

 

[dave2018]

Hi, I'm not sure if formulas can "remember" previous situations - so to speak.

Is there a logical order for your data's order?

[Disclaimer: Don't Give up hope! There's always a solution...]

Maybe VBA is the way to go... I will leave that to the guys with more than 2,000 posts

 

[sheetspread]

Paste the new list below the source list and update the formula references.

 

[dave2018]

You're right, but actually it would be interesting to note if it can be done without changing the data source, AND have a robust formula...

 

[sheetspread]

As you said, formulas won't remember the original order. VBA can, but the worksheet solution is simplest.

 

[mwscarponi]

Is there a logical order for your data's order?

Yes, but no. It's not alphabetical, or numerical or anything, but it has a reason for the order.

Paste the new list below the source list and update the formula references.

The problem is the source list is constantly gaining new entries.

The source list has 36 possible entries, and the entries are "slotted," but are filled in randomly (as available). So really it's something like this:

(Blank)
(Blank)
Orange
(Blank)
Red
Orange
(Blank)

Where any blank will be filled in with another color and the worksheet information updated. Doing this manually every time wouldn't be worth it.

 

[sheetspread]

It will still sort previous names first. You can name a dynamic range rather than manually extend it each time. Do you want blanks excluded? That is possible, but will clutter the formula. I think you might be better off with the advanced filter.

 

[dave2018]

I understand that for You it's logical, but I was asking if it is for excel. And if not, then..


what about this interesting idea, every time you add an item, you add the current date/time that u added it in the next column over?
(a handy shortcut by-the-way is by pressing CTRL+; for the date or CTRL + SHIFT + ; for the time, Or to insert both, do the first one, then press the spacebar, and finally do the 2nd shortcut...[FONT=Segoe UI, Segoe UI Web, Segoe UI Symbol, Helvetica Neue, BBAlpha Sans, S60 Sans, Arial, sans-serif])

If you can do that, we can then sort the item's based on their entry time...[/FONT]

 

[mwscarponi]

Paste the new list below the source list and update the formula references.

Doing this manually wouldn't be worth it

You can name a dynamic range rather than manually extend it each time.

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

Do you want blanks excluded? That is possible, but will clutter the formula. I think you might be better off with the advanced filter.

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

 

[mwscarponi]

I understand that for You it's logical, but I was asking if it is for excel. And if not, then..


what about this interesting idea, every time you add an item, you add the current date/time that u added it in the next column over?
(a handy shortcut by-the-way is by pressing CTRL+; for the date or CTRL + SHIFT + ; for the time, Or to insert both, do the first one, then press the spacebar, and finally do the 2nd shortcut...)

If you can do that, we can then sort the item's based on their entry time...

That might be a workaround worth considering. Would I have to manually instigate the sort each time, or could that be automatic?