I would like to have a query substitute a 0 with a blank cell. Here is what I have.
First, here is a query that returns two columns, the first with text and the second with the count.
<code>=query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''")
</code>
<code>=query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''")
</code>
=query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''")
I want to duplicate this result in the same column, but with a 0 result (all of the values in the text are unique, so all the results are 1). So I put the following:
<code>={query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''");query(A5:C,"select Col2, count(Col2)-1 where Col2 is not null group by Col2 label Col2 '', count(Col2)-1 ''")}
</code>
<code>={query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''");query(A5:C,"select Col2, count(Col2)-1 where Col2 is not null group by Col2 label Col2 '', count(Col2)-1 ''")}
</code>
={query(A5:C,"select Col2, count(Col2) where Col2 is not null group by Col2 label Col2 '', count(Col2) ''");query(A5:C,"select Col2, count(Col2)-1 where Col2 is not null group by Col2 label Col2 '', count(Col2)-1 ''")}
So the result is something like this:
text1 | 1
text2 | 1
text3 | 1
text1 | 0
text2 | 0
text3 | 0
What I would like is to have all of the 0 results have a blank cell, like this
text1 | 1
text2 | 1
text3 | 1
text1 |
text2 |
text3 |
Is there a way to accomplish this?
Thanks,
Doc