How to identify duplicate values only if they appear inconsecutively?

[salt_wagonner]

I have a data set with many duplicate values. I need to find duplicates from among a list of duplicates only if they appear inconsecutively. I know I'm not explaining this very well, so this picture might help:

In this example, "Apple" would be the only duplicate value I would want to list, because it appears in two separate groups. I don't care about the duplicate values that are consecutive to each other (rows 1-3, and 11-12), I care that the same value appears inconsecutively (row 2, row 11).

I have some code that I found on this site that works to identify and list the duplicate values, but it doesn't work for the functionality as I described it above. I'm not sure how to go about changing it, so if anyone has suggestions I would greatly appreciate it!

Sub TestForDuplicates()
    Set r = Range("C2", Range("C" & Rows.Count).End(xlUp))
    For Each c In r
        If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & vbCr & c
    Next
    MsgBox IIf(s <> "", "Found dup" & vbLf & Mid(s, 2), "No dup")
End Sub

 

[JamesCanale]

A no-code version

MrExcelPlayground16.xlsx
	A	B	C	D
10	Apple	TRUE		Apple
11	Apple	TRUE		Potato
12	Apple	TRUE
13	Banana	FALSE
14	Banana	FALSE
15	Apple	TRUE
16	Apple	TRUE
17	Potato	TRUE
18	Potato	TRUE
19	Squash	FALSE
20	Squash	FALSE
21	Squash	FALSE
22	Potato	TRUE
Sheet10

Cell Formulas
Range	Formula
D10:D11	D10	=UNIQUE(FILTER(A10:A22,B10:B22))
B10:B22	B10	=LET(a,A10=$A$10:$A$22,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))
Dynamic array formulas.

 

[salt_wagonner]

A no-code version

MrExcelPlayground16.xlsx
	A	B	C	D
10	Apple	TRUE		Apple
11	Apple	TRUE		Potato
12	Apple	TRUE
13	Banana	FALSE
14	Banana	FALSE
15	Apple	TRUE
16	Apple	TRUE
17	Potato	TRUE
18	Potato	TRUE
19	Squash	FALSE
20	Squash	FALSE
21	Squash	FALSE
22	Potato	TRUE
Sheet10

Cell Formulas
Range	Formula
D10:D11	D10	=UNIQUE(FILTER(A10:A22,B10:B22))
B10:B22	B10	=LET(a,A10=$A$10:$A$22,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))
Dynamic array formulas.

My date set has thousands of rows, so I need it to be a bit more automated, but thank you for commenting.

 

[salt_wagonner]

A no-code version

MrExcelPlayground16.xlsx
	A	B	C	D
10	Apple	TRUE		Apple
11	Apple	TRUE		Potato
12	Apple	TRUE
13	Banana	FALSE
14	Banana	FALSE
15	Apple	TRUE
16	Apple	TRUE
17	Potato	TRUE
18	Potato	TRUE
19	Squash	FALSE
20	Squash	FALSE
21	Squash	FALSE
22	Potato	TRUE
Sheet10

[/XtR]

[XR]

Cell Formulas
Range	Formula
D10:D11	D10	=UNIQUE(FILTER(A10:A22,B10:B22))
B10:B22	B10	=LET(a,A10=$A$10:$A$22,b,a*SEQUENCE(ROWS(a)),c,FILTER(b,b>0),d,ROWS(c),e,INDEX(c,1)+d-1,f,INDEX(c,d),IF(f<>e,TRUE,FALSE))
Dynamic array formulas.

I've been playing around with your formula and I can get it to work with one word strings, but not with longer strings, which is what my data has. Can you tell me if the formula should be able to work for longer strings?

 

[Peter_SSs]

What about this, without the helper column?

23 03 13.xlsm
	A	B	C
1	Data		Split
2	Apple		Apple
3	Apple		Potato
4	Apple
5	Banana
6	Banana
7	Apple
8	Apple
9	Potato
10	Potato
11	Squash
12	Squash
13	Squash
14	Potato
15
Split Data

Cell Formulas
Range	Formula
C2:C3	C2	=LET(r,A2:A14,u,UNIQUE(r),rw,ROW(r),FILTER(u,(XLOOKUP(u,r,rw,,,-1)-XLOOKUP(u,r,rw)+1)<>COUNTIF(r,u),""))
Dynamic array formulas.