0

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

  • The best way is very subjective. Are you looking for a formula, macro/script? Have you already made some search/research efforts? – Rubén May 24 '18 at 22:32
  • Formula would be preferred for myself because that's the type of thing I'm used to and I'm much more familiar with syntax in them. Presently I'm not sure where to start, due to the way I have it laid out. This is a decent example sheet for what would be input into it (docs.google.com/spreadsheets/d/…). Something that happens in a hidden sheet with formulas (to be readable) is the Category and say Item 1 from both types get turned into a number, which is used to identify that specific combination. However I don't want multiple sets of type 1. – JarheadHME May 24 '18 at 23:04
  • Honestly the end to all of my problems was if I could make it so that in the form, the four dropdown boxes were linked together so that when an option was selected in one, the option disappeared from the other, but I did quite a bit of searching and couldn't find a way to do that. – JarheadHME May 24 '18 at 23:07
  • Those are important details so they should be included in the question. Please edit the question to include them. – Rubén May 24 '18 at 23:25
  • Done, added all the extra info that was put in those two comments. – JarheadHME May 24 '18 at 23:40
0

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.

  • Hm, is there another alternative? This could potentially work, but would be a mild pain. I forgot to mention that there's 45 potential options for the type in question. Really sorry about that... I could do it transposed, with the item options being the rows and the selection of them being along the top, but I think that might be a pain to work with... – JarheadHME May 25 '18 at 0:08
  • @JarheadHME How many categories do you have? – Rubén May 25 '18 at 0:26
  • Right now, there are 11 possible categories, but soon there will be another (it's stuff based around a game) and in the coming year or so there will be a few more so I left some room in the id's for more to be added in the future. But yeah to directly answer the question, there is 11 possible categories. That one is a multiple choice question however, and works just fine with how I have it set up, though if changing it would help... – JarheadHME May 25 '18 at 0:29
  • "Using a formula could be an option if you are open to have a second sheet to display the results" Well I do have an entire sheet hidden dedicated to this kind of stuff, so I'm open to it. I just don't know where to go with it myself... – JarheadHME May 25 '18 at 2:40
0
  • 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, ))}

    0


0

0

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
  1. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.