1

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

3
  • only if there are different vendors or for all cases?
    – user0
    Jun 14, 2019 at 18:46
  • for all cases :)
    – D-Ly
    Jun 14, 2019 at 18:48
  • Also to note, some vendors will provide duplicates within their own list and I would like to display that as well.
    – D-Ly
    Jun 14, 2019 at 18:56

1 Answer 1

0

=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

5
  • if you want all duplicates (not all unique duplicates) then remove all UNIQUE and PROPER functions from the formula
    – user0
    Jun 14, 2019 at 19:31
  • In the duplicate table, would it be possible to also lookup which vendor has the most cost-effective price?
    – D-Ly
    Jun 20, 2019 at 15:29
  • @D-Ly most likely, yes
    – user0
    Jun 20, 2019 at 17:45
  • what would be the formula?
    – D-Ly
    Jun 20, 2019 at 19:10
  • sheet you shared in your question is private so hard to tell based on no reference point
    – user0
    Jun 20, 2019 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.