@melvin7020,
the targets of your comments are ambiguous, we do not know to whom you are replying.
you had accepted the post by Villeroy as the solution but now I see that you have changed your mind and accepted my answer.
.
I originally composed the query fairly quickly and checked only for correct duplication.
next day I checked every row and discovered an error, I had failed to skip the previously duplicated data from subsequent rows, so I fixed it.
to avoid creating any confusion I did not post the updated code and awaited feedback.
.
so here is the code which exactly reproduces the contents of your spreadsheet, it requires direct mode:
- open “Cell_Ref_fb.odb”
- hit Queries icon
- hit ‘Create Query in SQL View’
- menu:>Edit>Run SQL command directly
- paste the code
- hit F5 to execute
with t as
(
select
r,
lag(txt,4) over(order by r) four_before,
lag(txt,3) over(order by r) three_before,
lag(txt,2) over(order by r) two_before,
lag(txt) over(order by r) one_before,
txt
from
(
select
data.*,
row_number() over(order by id) r,
case when lag(txt) over(order by id) = txt then 1 else 0 end s
from
data
)
where s = 0
)
,
Dupes as
( select
r,
row_number() over(order by r) r2
from
(
select
row_number() over(order by id) r,
case when lag(txt) over(order by id) = txt then 1 else 0 end s
from
data
)
where s = 1
)
,
Gaps as
(
select
min(r) mn, max(r) mx
from Dupes
group by r - r2
)
,
Fills as
(
select t1.* from
Gaps g
join
(select r from Dupes) d
on d.r between g.mn and g.mx
join
(select * from t) t1
on g.mn -1 = t1.r
)
--select * from t
--select * from Dupes
--select * from Gaps
--select * from Fills
select
four_before, three_before, two_before, one_before, txt
from
(
select * from Fills
union all
select * from t
)
order by r
when using a spreadsheet you have to show duplicates but using SQL we can easily remove them.
here is a short piece of code which removes the duplicated rows from the result set:
select
id,
lag(txt,4) over(order by id) four_before,
lag(txt,3) over(order by id) three_before,
lag(txt,2) over(order by id) two_before,
lag(txt) over(order by id) one_before,
txt "CURRENT"
from
(
select
data.*,
lag(txt) over(order by id) <> txt flag
from
data
)
where coalesce(flag, true)
order by id
