CODEWITHSUNDEEP
google-sheets
mau
mau

Reputation: 195

Retrieve duplicates based on a column

I have the following sheet where I need to retrieve only duplicates based on the column K in this example. Please bear in mind that I actually have over 10k data and I need to retrieve them from a different spreadsheet, but I could use some help with the formula.

Thank you.

enter image description here

Upvotes: 0

Views: 30

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

This formula should work for you:

=ArrayFormula({J1:L1; FILTER(J2:L,J2:J<>"",COUNTIF(K2:K,K2:K)>1)})

The curly brackets { } allow us to build a virtual array.

J1:L1 will place your original headers at the top.

The semicolon means "move down to the next row" (i.e., place the results underneath the headers).

FILTER will filter in only entries where Col J is not blank and where there the COUNTIF from Col K is more than 1 (i.e., where there are duplicates).

If the formula does not work, you are likely in an international locale that uses semicolons as parameter delineations. In that case, use this version of the formula:

=ArrayFormula({J1:L1; FILTER(J2:L;J2:J<>"";COUNTIF(K2:K;K2:K)>1)})

Upvotes: 2

Related Questions