CODEWITHSUNDEEP
google-sheets
MegaMikeJr
MegaMikeJr

Reputation: 145

How do you only show duplicate rows based on a cell?

I am wanting to show all rows through either a query function or array formula that have a duplicate cell reference in Column B and where it shouldn't matter if Column A doesn't match.

Column A Column B
Title 1 URL 1
Title 4 URL 4
Title 2 URL 2
Title 2 URL 2.1
Title 3 URL 3
Title 5 URL 2
Title 7 URL 1

The result should be the following:

Column A Column B
Title 1 URL 1
Title 7 URL 1
Title 2 URL 2
Title 5 URL 2

How do I accomplish only showing duplicate records with a unique records in Column A?

Upvotes: 0

Views: 38

Answers (2)

Aresvik
Aresvik

Reputation: 4620

Try this:

=arrayformula(query({"Dupe",A1:B1;if(B2:B<>"",if(countif(iferror(vlookup(B2:B,{B:B,row(B:B)},2,false),""),row(B2:B))=1,,true),),A2:B},"select Col2,Col3 where Col1 = true order by Col3,Col2",1))

enter image description here

Or without query:

={A1:B1;sort(filter(A1:B,countif(vlookup(B1:B,{B:B,row(B:B)},2,false),row(B1:B))<>1),2,1,1,1)}

Upvotes: 2

player0
player0

Reputation: 1

try:

=UNIQUE(FILTER(A1:B, 
 COUNTIFS(B1:B, B1:B, ROW(B1:B), "<="&ROW(B1:B))>1))

Upvotes: 1

Related Questions