What would be the best way to remove duplicate items from cells in rows? For instance...
Category 1 | Item 1 | Item 2 | Item 2 | Item 3
Category 2 | Item 3 | Item 1 | Item 2 | Item 3
Category 3 | Item 1 | Item 1 | Item 1 | Item 1
Category 2 | Item 2 | Item 3 | Item 2 | Item 1
Category 3 | Item 1 | Item 3 | Item 2 | Item 2
I want it to keep only the first of each unique item in each row. So the ideal result here would be...
Category 1 | Item 1 | Item 2 | | Item 3
Category 2 | Item 3 | Item 1 | Item 2 |
Category 3 | Item 1 | | |
Category 2 | Item 2 | Item 3 | | Item 1
Category 3 | Item 1 | Item 3 | Item 2 |
Note that this is intended to cover a 4-wide infinitely tall array and do its work as form entries got filled in.
Here's an example sheet with information the form would be inputting into it. I'm not entirely sure where to start for the reason that the way I need it done here and not somewhere else is that in a hidden sheet (functioning as a sort of back-end, has loads of functions in it with array formulas and other arrays in general that get referenced from other tabs).
The way it actually works is it takes an item from Type 1
(in this example it'll all be done based on row 2), turns it into a number from a big table, adds the number from another table that corresponds to the category that the initial item was in (i.e cat 1
), and then finally adds the corresponding item from type 2
(so the item in the Type 2 Item 1
column).
Basically, if I get items in Type 1
in the same row that are the same, I need it to remove extras. It can leave stuff in Type 2
alone, as they're inconsequential without something in Type 1
.
If I could do something in the form to make it so that the 4 drop-down boxes were linked together so that when one item in one of them was selected, then that same option would be removed from all the others, that would solve my problems, but I did a fair bit of searching and couldn't find a way to do that. The reason I'd prefer sticking to drop down boxes, by the way, is because there are 45 options for Type 1