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

• 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

Answer 2

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 3

Formula based solution

=if(countif($A1:A1,A1)=1,A1,)

Assumptions

1. Data has the following shape

  |      A      |    B     |    C     |    D     |    E
--+-------------+----------+----------+----------+---------
1 | Category 1  |  Item 1  |  Item 2  |  Item 2  |  Item 3
2 | Category 2  |  Item 3  |  Item 1  |  Item 2  |  Item 3
3 | Category 3  |  Item 1  |  Item 1  |  Item 1  |  Item 1
4 | Category 2  |  Item 2  |  Item 3  |  Item 2  |  Item 1
5 | Category 3  |  Item 1  |  Item 3  |  Item 2  |  Item 2

2. The above formula could be added to the same sheet

Basic instructions

1. Add the formula to G1
2. Copy G1
3. Select a range of the same size as the original data. In this case G1:K5.
4. Paste

The result should look like this:

If the formulas could not be on the same sheet, replace A1 by a reference of the form Sheet1!A1 where Sheet1 is the name of the sheet holding the source data.

How the formula works

$A1:A1

1. $A1 is the start cell. It is as semi-absolute reference, the column is absolute but the row is relative.
2. A1 is the end cell. It is a relative reference.
3. When the formula is copied to a n x m size range the relative references of the formula copies will be automatically adjusted by Google Sheets.
4. countif($A1:A1,A1) The obvious: This part counts how many cells of the first reference have the value of the second reference. The trick: The first reference has a size of 1 x 1 on G1, on H1 it has a size of 1 row x 2 columns, on I1, it has a size of 1 row x 3 columns, an so on.
5. When the above part is equal to 1, it adds the value of the second reference, otherwise it adds a blank (empty cell).

Remarks

If the real case has thousands for source cells, the spreadsheet recalculation time will be long. Bear in mind that any edit to the sheet will trigger the recalculation. If this bother you, something like "Spreadsheet Freezer" could help. Disclaimer: I'm the developer of this add-on.

Related

• How do I install Spreadsheet Freezer?