Google-sheets – Filter and display duplicates in a table

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing

The master data will show:

enter image description here

And I would like to have a table that displays the duplicates alongside the title and vendor based on the ISBN:

enter image description here

Best Answer

=ARRAYFORMULA({UNIQUE(ARRAY_CONSTRAIN(FILTER({B2:B, PROPER(C2:C), A2:A}, 
 COUNTIF(B2:B, B2:B)>1), ROWS(A2:A), 2)), IFERROR(VLOOKUP(UNIQUE(ARRAY_CONSTRAIN(
 FILTER({B2:B, PROPER(C2:C), A2:A}, COUNTIF(B2:B, B2:B)>1), ROWS(A2:A), 1)), {QUERY(
 QUERY(ARRAY_CONSTRAIN(UNIQUE(FILTER(A2:C, COUNTIF(B2:B, B2:B)>1)), ROWS(A2:A), 2), 
 "select Col2,count(Col2) where Col1 is not null group by Col2 pivot Col1", 0), 
 "select Col1 offset 1", 0), SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 SUBSTITUTE(IF(ISNUMBER(QUERY(QUERY(ARRAY_CONSTRAIN(UNIQUE(FILTER(A2:C, 
 COUNTIF(B2:B, B2:B)>1)), ROWS(A2:A), 2), 
 "select count(Col2) where Col1 is not null group by Col2 pivot Col1", 0), 
 "offset 1", 0)), INDEX(QUERY(ARRAY_CONSTRAIN(UNIQUE(FILTER(A2:C, 
 COUNTIF(B2:B, B2:B)>1)), ROWS(A2:A), 2), 
 "select count(Col2) where Col1 is not null group by Col2 pivot Col1", 0), 1, ), ), 
 " ", "♦")),,999^99))), " ", ", "), "♦", " ")}, 2, 0))})

0

Related Topic