Remove duplicate items from array?

Question

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

Answer 1

If you want to remove the repeating Items each time a new response is sent, you will have to use a script or an add-on.

Using a formula could be an option if you are open to have a second sheet to display the results or to redo the cleaning up of the data on an ongoing basis.

---

The OP commented that there are too many Types and categories (45 and 11 respectively) so a multiple choice question will not help.

---

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.

Consider to change the structure of your form. Instead of using 4 dropdown questions, use 1 multiple choice grid question. This kind of question allows you to limit to select one option by column.

Answer 2

• paste in G1 cell and drag down:

  ={A1, ARRAYFORMULA(IF(TRANSPOSE(IF(LEN(
   TRANSPOSE(B1:E1)), IF(((COUNTIF(
   TRANSPOSE(B1:E1), 1)<>COUNTIF(
   TRANSPOSE(B1:E1), 
   TRANSPOSE(B1:E1)))+(MATCH(
   TRANSPOSE(B1:E1), 
   TRANSPOSE(B1:E1), 0)1, , "♠"), ))="♠", B1:E1, ))}

  

---

• demo spreadsheet