Sorting data and labelling data (and preserving the labelling for other data sets)

Hi everyone,

I have a big set of DATA that I want to sort-out and label each unique item in a predefined way. Items can be labelled as ‘System’, ‘Module’, ‘Component’ or ‘Method’.

The DATA is the result of filtering a large set of DATA (currently 90 Columns and 900 Rows) but the same analysis I want to perform for different sets of DATA.
The complete set of data has about 1000 unique items which all need to be labelled manually.

The DATA is available in an array. In the attached example I took five columns and 8 rows. As you many notice some items are repeating.

As a first step I want to select all the unique items in the set. On tab (‘Selection of DATA’) I showed the requested result and how it can be done manually. Is it possible to automate the selection of all unique items and remove all the duplicates with formula (if so, how)?

As a next step I want to have all the unique items sorted out in one column. For me then the task to manually label each unique item separately. See Tab ‘Labelling data’. Labelling is predefined as indicated in F3:F6 (and mandatory when labelling each item).

It would be very nice if I can (re)use labelling done for a previous set of DATA when a new DATA set is generated and analyzed.

The desired result is to have an overview per row about the chain of item1, label, item2, label, item3, label, … (see TAB: Result).

How can these steps be done in the most efficient/automated and robust way? Many of the steps now are done manually. I tried different ways myself, but e.g. Unique only is working per (separate) column.

Manually labelling is pretty time consuming so preserving that work from a previous analysis is important.

Thanks for reading, your help and looking forward to your idea(s)/suggestions!


Best regards, Jan