Confirm that values in a column are alternating

Question

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.

Answer 1

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

Answer 2

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")