CODEWITHSUNDEEP
google-sheets
BhishanPoudel
BhishanPoudel

Reputation: 17164

How to find count of items in googlesheet where cell can have leading and trailing spaces?

I was trying to find the count of cities having COUNT > 1 in google sheet but the problem is that some of the cities have leading or trailing spaces.

Shared google sheet: https://docs.google.com/spreadsheets/d/10NzbtJhQj4hQBnZXcmwise3bLBIAWrE0qwSus_bz7a0/edit#gid=8060956

My attempt

=ArrayFormula(QUERY(D1:D&{"",""},"select Col1, count(Col2) where Col1 != '' group by Col1 order by count(Col2) desc",1))

Data:

SN  Company         Place                City
1   Cardinal Health Dublin, OH           Dublin
2   McDonald        Chicago, IL           Chicago
3   Nielson         Chicago, IL, NY, FL  Chicago
4   Edelman         Dublin, OH           Dublin
5   Test            Columbus, OH         Columbus
6   ABC             Los Angeles, CA      Los Angeles
7   DEF             Chicago, IL          Chicago

Required

- trim white spaces around city
- sort by desc count
- count > 1


City      Count
Chicago   3
Dublin    2

Upvotes: 0

Views: 51

Answers (1)

doubleunary
doubleunary

Reputation: 19040

Try this:

=arrayformula( query( query( trim(D1:D), "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc", 1 ), "where Col2 > 1", 1 ) )

The inner query() gets cities whose count is greater than or equal to one, and the outer query() filters the result so that the counts are greater than one.

Upvotes: 1

Related Questions