1

I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values.

An example of A1:A20 could be as follows: {a,b,a,b,a,b,a,b,a,b,a,b,b,a,b,a,b,a,a,b}. (In my actual data these values may be different, however the entire list of cells is guaranteed to begin with "a" and end with "b").

In columns B and C, I have some other values (in my actual spreadsheet they are dates). Each "a" or "b" in Column A corresponds to two dates in the same row (one in column B, and one in column C).

My goal is to remove duplicate As and Bs (in the manner described below), along with the dates associated with these duplicate values. I should be left with a list of alternating As and Bs — matched with the same dates as in the original data (all columns move synchronously).

The rules for deleting duplicates are as follows:

  • If there are two "a" consecutively, remove the first "a".
  • If there are two "b" consecutively, remove the second "b".
  • Note that there may be more than two consecutive As and Bs. In such a case, the first two should be evaluated and one removed, then repeat for the next two, etc until one remaining.

I believe this could best be accomplished with a filter (maybe a query?) somehow, but I was unable to get it to work or even seem close. Using the above example, A13 (and corresponding B13:C13) and A18 (and corresponding B18:C18) should be removed. The resulting A1:A20 (after values below are shifted up) should read {a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,...}. Every remaining value in column A should still be paired with the original value from column B.

As an alternative semi-solution, would it be possible to write a conditional formatting script to highlight the duplicate values? It is not too difficult to delete the repeating values (there are very few) manually, but very difficult to tell which ones to delete without them being highlighted. I tried highlighting "a" one colour and "b" another to look for non-alternating patterns, but it was still difficult to quickly recognize.

  • Possible solution would be =FILTER(A1:C,A1:A<>A2:A). This almost works, except instead of removing the second "b", it removes the first. Any idea how it could be refined to work correctly? – Kwilinsi Jul 16 '18 at 3:19
  • are columns D-Z avail? – user0 Jul 16 '18 at 4:45
  • As far as you should be conscerned in answering, yes D-Z are available (also AA-AH as you ended up using). In my actual sheet, however, I had to work on this in a completely separate spreadsheet because I was at the 2000000 cell limit already. – Kwilinsi Jul 16 '18 at 13:28
0

it can be done with brute force :)

here's the sheet: https://docs.google.com/spreadsheets/d/

you can put calculations from D to AE into separate sheet or just hide those columns

ofcourse AF (respectively AG, AH) can be dragged down for the population. select AF2:AF5 and drag down the blue square. also you can drop half of those columns - I just left them there, so you could see the whole process of getting final result

  • Wow, thats a lot of brute forcing. I did end up coming to a solution myself, but was looking to see if anyone had a better one. Mine was pretty similar to this one; however it didn't take as long since I didn't do any reversed order or anything (whats that for?). I basically made one filter for the As, one for the Bs, and then a function to combine them alternated. – Kwilinsi Jul 16 '18 at 13:25
0

Here's a sample custom function for you to learn,research,debug and modify to your liking:

function DupeDestroyX(range,a,b,index) {
  var j = index-1 || 0;
  for(var i=0;i<(range.length-1);i++){ //loop over all entries
    try {
      if(range[i][j]==a && range[i+1][j]==a) { // if current entry is a and next entry is also a
        range.splice(i,1); // delete current entry    
        if (range[i+1][j]==a) {i--} //If next entry is also a (after deleting),loop repeat this iteration
      }
      if(range[i][j]==b && range[i+1][j]==b) {
        range.splice(i+1,1); //delete next entry
        if (range[i+1][j]==b) {i--}
      }
    }
    catch(e) {continue;}
  }
  return range;
}

Usage:

=DupeDestroyX(A1:C,"a","b")

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.