Excel Cells Populated with Text, Count Only Once with Blanks 3

[akrshaw]

I have tried several solutions to resolve this issue, which are not working.

I have the below:

Bid MGR
Fred
Jane

Mike
Jane

Greg
Fred

I need to have this cell count the number of individual Bid MGRs, ignoring the blanks and duplicates.

Thanks

 

[bluedragon2]

A pivot table is the quickest way to get the data you are looking for. Other then that, sorting the list and applying advanced filtering to weed out the duplicates.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[randy700]

I have tried several solutions to resolve this issue, which are not working.

Such as CountA?


Randy

 

[bluedragon2]

Randy,

COUNTA will count non blank entries, but will not account for duplicates.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Using a NAMED RANGE

and...

replacing each BLANK cell with, for instance, a SPACE, then
[tt]
=SUM(IF(FREQUENCY(MATCH(Bid_MGR,Bid_MGR,0),MATCH(Bid_MGR,Bid_MGR,0))>0,1))-1
[/tt]
entered as an ARRAY FORMULA.

I get 4 using your data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

Skip,

The FREQUENCY function caught my eye, I will have to go back and take an in-depth look at it. May come in handy on some other functions I was developing.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I cannot take credit for this formula.

I think I might have ripped it off of xlbo, KenWright, AnotherHiggins or GlennUK.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akrshaw]

Skip

I created the Named Range Bid_MGR, but I cannot add a space to the blanks, as another formula then sees that that cell is no longer blank and counts it for another cell.

Plus, I am not the daily user of the spread sheet, asking someone to add a space to every cell would not work.

 

[bluedragon2]

Other then the suggestions above, creating a user defined function would probably be your best bet. I will leave it to the good VBAers to help with that.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

the MATCH() function returns #N/A! for each EMPTY cell.

I'd use a PivotTable or MS Query to summarize

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

You could include an additional column in which each cell is a copy of the equivalent cell in your manager data unless the data cell is empty, in which case it is space. Then make THAT column your named range. Then use Skip's method.

Alternatively, if you prefer a VBA function, here is what I use:

Public Function AL_CountUnique(rng As Range) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Description:
'This function returns the count of the number of unique cells in the supplied range.
'It does so by creating an empty collection, then going through all the cells in the
'supplied range and, if they are not already contained in the collection, adding them.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'dim required vars
Dim MyCol As Collection
Set MyCol = New Collection
Dim mycount As Long
Dim found As Boolean
Dim xtext As String
Dim x As Variant, y As Variant
Dim chckrng As Range

'initiate error handling
'we will need to do stuff which, under some circumstances we know will create an error
'we then want to test if an error has occurred as the next line, so we do NOT want an error
'to cause prog execution to go to a routine, but just continue with the next step.  Hence,
'"Resume Next"
On Error Resume Next
Set chckrng = Application.Intersect(rng, rng.Worksheet.UsedRange)
mycount = 0

'check each cell individually
For Each x In chckrng
    'find a suitable string for each cell
    'If the cell is empty, the string is "Empty"
    'If it contains text then the string is the text
    'If it contains a numerical value, then the string is the text version of the value
    If IsEmpty(x) Then
        xtext = "Empty"
    Else
        If VarType(x) And vbString Then
            xtext = x.Text
        Else
            xtext = Trim(Str(x.Value))
        End If
    End If
    
    'check if this string has been used as a key to the collection
    found = False
    If mycount > 0 Then
        Set y = MyCol(xtext)
        If Err.Number = 0 Then
            found = True
        Else
            Err.Clear
        End If
    End If
    
    'if the key is not found, this value is unique, so add it to the collection
    If Not found Then
        MyCol.Add x, Key:=xtext
        mycount = mycount + 1
    End If
Next x

AL_CountUnique = mycount
End Function

I hope that helps.

Tony

 

[xlbo]

try this if you cannot remove blanks:
=SUM(IF(FREQUENCY(MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0),MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0))>0,1))-1

entered as an array formula still (ctrl+shift+enter)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

ps Skip - pretty sure it wasn;t me that gave toy the initial formula - I'm not very good with array formulae - I normally stick to sumproduct...not very useful in this case though!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

I would not want to give short shrift to a respected Brit transplant Aussy. Hope all is well down under.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

All going great skip though it is a bit harder to get in on threads over here due to the time diff!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

if you're allowed a helper column it's really easy.
My data are in column A and I've ignored headers. Named ranges are of course better. My helper column is B, which contains the following formula:
=IF(A1<>"", IF(MATCH(A1,A:A, 0)=ROW(), 1, 0), 0)
This places "1" in the cell corresponding to the first instance of any text. Then sum column B for the total number of bid MGRs.

 

[N1GHTEYES]

Nice formula xlbo - now that I've figured out what it is doing. Thanks for introducing me to the FREQUENCY function.

However, I think the -1 on the end is superfluous. Presumably it is there to deal with the extra cell added to the Frequency() return array to display the number of items above the highest bin. But, given that you are using Bid_Mgr to provide the bins as well as the source, there are none. In other words, if there are no blanks in the list, the answer comes up 1 short.

The upshot is I checked it with several different sets of data, with and without repeats and with and without blanks, and it always seems to come up one short if there are no blanks. So, I'd say the right formula is:

SUM(IF(FREQUENCY(MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0),MATCH(IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),IF(LEN(Bid_Mgr)=0," ",Bid_Mgr),0))>0,1))-IF(SUM(IF(Bid_Mgr="",1))>0,1,0)

Tony

 

[xlbo]

I jus' stole Skip's formula and added the check for blanks - taking advantage of the array formula processing to inspect each cell as part of the array "Bid_Mgr" and check to see if it is blank or not

Looks like you are right on the superfluous -1 though...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

I nearly forgot...

Thanks Skip for introducing me to Frequency() and your use of it with the match() function.

Thanks xlbo for the simple & obvious use (which hadn't occurred to me! Doh!) of using len()=0 to designate blank cells.

Have stars.

Tony

 

[SkipVought]

@officeexcel

He wants the count of UNIQUE values.

so this UNIQUE count would be 2
[tt]
Dan
Dan
Sam

Sam
Dan
Sam
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]