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

  • only if there are different vendors or for all cases? – user0 Jun 14 '19 at 18:46
  • for all cases :) – D-Ly Jun 14 '19 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 '19 at 18:56
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

| improve this answer | |
  • if you want all duplicates (not all unique duplicates) then remove all UNIQUE and PROPER functions from the formula – user0 Jun 14 '19 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 '19 at 15:29
  • @D-Ly most likely, yes – user0 Jun 20 '19 at 17:45
  • what would be the formula? – D-Ly Jun 20 '19 at 19:10
  • sheet you shared in your question is private so hard to tell based on no reference point – user0 Jun 20 '19 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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