1

I have a sheet of products and skus - I would like to have a formula that searches the entire column for matching text strings and returns it in a separate cell as the text string

Is this possible?

enter image description here

1 Answer 1

0

You can use a FILTER with COUNTIF

=UNIQUE(
  FILTER(
   B28:B,
   COUNTIF(B28:B,B28:B)>1))

If you wanted to also return the name, change the first range in the FILTER to A28:B

Since it will return every instance, we need to wrap it in UNIQUE.

For an overall count,

=QUERY(
  {A28:B},
  "select Col1, Col2, Count(Col1)
   where Col2 is not null
   group by Col1, Col2
   label
    Col1 'Item',
    Col2 'SKU',
    Count(Col1) 'Total'")

This will do both the name and the SKU. If you only want to run the SKU

=QUERY(
  {B28:B},
  "select Col1, Count(Col1)
   where Col1 is not null
   group by Col1
   label
    Col1 'SKU',
    Count(Col1) 'Total'")

If you only want to pull the ones where you have more than one and want to use QUERY, you could use this or wrap the previous QUERY with another QUERY and use where Col2 >1 -- but this one is cleaner.

=ARRAYFORMULA(
  QUERY(
   UNIQUE({B2:B,COUNTIF(B2:B,B2:B)}),
   "where Col2 >1
    label
     Col1 'Item',
     Col2 'Total'"))
8
  • 1
    Definitely, @magelincs! I added them to my post. If these work out for you, can you mark it as answered? I need some of that hot reputation so I can vote on stuff :) QUERY is definitely worth getting good at. Its a database language and is perfect for stuff like this.
    – tomf
    Apr 28 at 22:00
  • 1
    Happy to help. Thanks for helping out with this rep thing. I'm new and powerless.
    – tomf
    Apr 28 at 22:04
  • 1
    no prob. You can either wrap it with another QUERY or use an array with the SKU and the output of a COUNTIF in the second column. Wrap that with UNIQUE and run it through a QUERY to pull items where the second column is greater than 1. I added this to the post above. Its basically the same output as the filter, but with headers defined in the formula.
    – tomf
    Apr 28 at 22:42
  • 1
    Amazing, thanks a lot Tom!
    – magelincs
    Apr 29 at 18:14
  • 1
    no problemo., Have a good one!
    – tomf
    Apr 30 at 6:53

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.