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?
Add a comment
|
1 Answer
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'"))
-
1Definitely, @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.– tomfApr 28, 2022 at 22:00
-
1Happy to help. Thanks for helping out with this rep thing. I'm new and powerless.– tomfApr 28, 2022 at 22:04
-
1no 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.– tomfApr 28, 2022 at 22:42
-
1
-
1