Find two consecutive rows that begin with same letter, delete the top one.

[User45]

Hello,
I have "A" column . A1 cell begins with letter "S". A2 begins with "K". A3 begins with "S" again.A4 begins with "K" again.

S...
K...
S...
K...
S...
K...

e.t.c

The problem is, sometimes there will be rows like

S...
Ky...
Kz...
St...
Sp...
K...

If you noticed the 2nd and 3rd one both begin with "K", while 4th and 5th both begin with "S".I'm looking for a spreadsheet formula that would delete the top one ,out of two consecutive rows with the same letter, so the final would be

S...
Kz...
Sp...
K...

Every cell has bunch of data in it but they always begin either with "S" or "K".
Not sure if I should open another post but my next step would be moving the "K" cells to "B" colum to make it look like
A B
----
S K
S K
S K
S K
my main problem is the first question, however.

I'm totally fine even if I have to go through a dozen steps, as long as it's a formula.Any help is appreciated.Thanks !

 

[Domenic]

Let's assume that A2:A7 contains the data, try...

C1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(LEFT(A2:A7,1)="S",ROW(A2:A7)),IF(LEFT(A2:A7,1)<>"S",ROW(A2:A7)))>0,1))

D1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(LEFT(A2:A7,1)="K",ROW(A2:A7)),IF(LEFT(A2:A7,1)<>"K",ROW(A2:A7)))>0,1))

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=C$1,INDEX($A$2:$A$7,SMALL(IF(LEFT($A$3:$A$8,1)<>"S",IF(LEFT($A$2:$A$7,1)="S",ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS(C$2:C2))),"")

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$2:D2)<=D$1,INDEX($A$2:$A$7,SMALL(IF(LEFT($A$3:$A$8,1)<>"K",IF(LEFT($A$2:$A$7,1)="K",ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS(D$2:D2))),"")

Notice that within the SMALL functions, the first range reference is offset from the second range reference by one cell.

Hope this helps!