+ Reply to Thread
Results 1 to 22 of 22

Counting unique text cells from muliple specific columns?

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Counting unique text cells from muliple specific columns?

    Hello,

    I have searched for ways to do this but I can't seem to get them to work. I want to count the number of text cells, excluding blanks and duplicates, from non successive columns.

    As an example:

    Mike Wilma Rox Rob
    Julie Izzy Bill Fred
    Ted Ed Mike Lynn
    (blank) (blank) (blank) (blank)
    Sue Jill John Liz

    I only want to count columns 1 and 3 and have it exclude the blank cells as well as not count the duplicates (in this case Mike), so the number returned should be 7 (Mike, Julie, Ted, Sue, Rox, Bill, and John)

    Thanks for the help!

    Robert

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    In all honesty for a single cell calc I think you would want/need a User Defined Function (VBA).
    IMO native functions will struggle to handle the non-contiguous sets.

    I've been looking at this for a while and though it's relatively straightforward to create a matrix in which names within even columns are discounted, ie:

    Please Login or Register  to view this content.
    utilising that array of values within a unique count is proving beyond me I'm afraid... others may have other ideas.

    A user defined function on the other hand would be pretty straightforward, ie:

    Please Login or Register  to view this content.
    the above, stored in a standard Module in a Macro Enabled file, can be called from a cell along the lines of:

    Please Login or Register  to view this content.
    you can pass an optional 2nd argument to stipulate case sensitivity - presumed insensitive (FALSE) unless otherwise specified
    Last edited by DonkeyOte; 08-11-2010 at 11:03 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Counting unique text cells from muliple specific columns?

    Maybe this,

    Please Login or Register  to view this content.

    Regards

  4. #4
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Counting unique text cells from muliple specific columns?

    Maybe this:

    Excel 2003:
    =SUM(IF(ISERROR(1/COUNTIF($A$1:$D$5,$A$1:$D$5)),0,1/COUNTIF($A$1:$D$5,$A$1:$D$5))*MOD(COLUMN($A$1:$D$1),2))

    Excel 2007:
    =SUM(IFERROR(1/COUNTIF($A$1:$D$5,$A$1:$D$5),0)*MOD(COLUMN($A$1:$D$1),2))

    entered with Ctrl+Shift+Enter

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    pb71, that is open to error I'm afraid should a name in either A or C be repeated in either B or D.

    sailepaty's formula works but is as I am sure he/she would agree limited in scope - ie additional ranges would be cumbersome.

    If flexibility is required then personally I think a UDF is only viable option - but that's my opinion of course

  6. #6
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Counting unique text cells from muliple specific columns?

    @DO,

    sailepaty's formula works but is as I am sure he/she would agree limited in scope - ie additional ranges would be cumbersome.
    sailepaty's formula doesn't work if there are duplicate names in either Columns A or C, hence me trying to give an alternative ... and failing

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    ha - serves me right for not testing either

    adapting the earlier approach

    Please Login or Register  to view this content.
    but again of very limited scope... and not particularly efficient

  8. #8
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Thank you for the all the replies. DonkeyOte's UDF works good. Is there an easy way to add additional condtions for AND IF BOLD and in another one with a condition AND IF contains "**". DonkeyOte's UDF is perfect if I change the formatting of the entire sheet, but would be best with two separate UDFs with the above conditions.

    Once again, thanks for all the help.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    Differentiating by format is generally ill advised, however, I'm still not entirely sure I follow all the requirements.

    Are you saying that you wish to have the option within the UDF call to include cells based on a pre-requisite font format and/or character ?

  10. #10
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Correct. Within the UDF have a condition that if the text is bold then count otherwise don't count. And then there can be another UDF that if a cell contains two asterisks like "**John" then count otherwise don't. I suppose it would be easiest to have three separate UDFs. One for total count, another for just bold, and another for any that have ** in the cells.

    What do you think. Thanks for helping!

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    Quote Originally Posted by eliopaulos
    I suppose it would be easiest to have three separate UDFs. One for total count, another for just bold, and another for any that have ** in the cells.
    Not really - when it comes to UDFs it's generally good to make a single UDF as flexible as possible.
    By adding some further arguments to the Function you can increase the flexibility

    I will post back later with a revised version - I'm heading off line for a bit just now I'm afraid.

    In the meantime - can you be specific in terms of the character requirement ?
    For ex. does the position of the character within the string have any significance ?
    ie *John and John* to be treated differently or the same ?

  12. #12
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    For the characters, **John and John** to be treated differently.

    Thanks.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    If (and that's a big if) I have understood correctly perhaps the below version is along the lines of what you want ?

    Please Login or Register  to view this content.
    The above as you can see has some additional optional parameters (in addition to pre-requisite range argument) that denote:

    1) font is bold (default is False)
    2) prefix (default is nothing)
    3) suffix (default is nothing)
    4) case sensitivity (default is insensitive)
    So in essence:

    Please Login or Register  to view this content.
    So if you wanted to simply count the unique values in the non-contiguous range of A1:A5 / C1:C5 you would use as before

    Please Login or Register  to view this content.
    If you wanted to further restrict based on those values for which font is bold

    Please Login or Register  to view this content.
    If you wanted to restrict to names prefixed with ** and bold then:

    Please Login or Register  to view this content.
    If bold font of no consequence you would change TRUE to FALSE (or remove the TRUE), ie

    Please Login or Register  to view this content.
    If you wanted to restrict to names with suffix of ** and bold then:

    Please Login or Register  to view this content.
    Case sensitivity is the final optional parameter

    Please Login or Register  to view this content.
    ie the above does the same as the predecessor only now Mike** and MIKE** would be seen as unique values

    One thing to note given the above - altering the Bold state of a cell is not a Volatile action (per se) and so will not invoke a calculation
    (ie changing Mike** to Mike** won't cause the UDF to recalculate unless done via an edit in the Formula Bar - ie re-entering the value)
    Last edited by DonkeyOte; 08-12-2010 at 04:32 PM. Reason: typo

  14. #14
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Thanks for getting back so quickly. Not sure what I'm doing wrong now, but no matter what I put in a cell to call the function always displays 0. Even tried just expressing a range, let alone other parameters, and it still returns 0.

  15. #15
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting unique text cells from muliple specific columns?

    how about using more func addin http://download.cnet.com/Morefunc/30...-10423159.html
    =SUMPRODUCT(--(UNIQUEVALUES(ARRAY.JOIN($A$1:$A$5,$C$1:$C$5))<>""))
    Or to ignore case
    array entered
    =SUMPRODUCT(--(UNIQUEVALUES(ARRAY.JOIN(LOWER($A$1:$A$5),LOWER($C$1:$C$5)))<>""))
    Last edited by martindwilson; 08-12-2010 at 07:27 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    Quote Originally Posted by eliopaulos View Post
    Not sure what I'm doing wrong now, but no matter what I put in a cell to call the function always displays 0. Even tried just expressing a range, let alone other parameters, and it still returns 0.
    Post a sample file.

  17. #17
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Here is a snippet from my larger file.

    Thank you for the continuing help.
    Attached Files Attached Files

  18. #18
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    error on my part - change:

    Please Login or Register  to view this content.
    to:

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Thank you for all the help. It works pretty well.

  20. #20
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    The use of "pretty" implies you may still have a few issues you wish to iron out ?

  21. #21
    Registered User
    Join Date
    08-11-2010
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Counting unique text cells from muliple specific columns?

    Only because it would be nice to not have Excel prompt recipients about macros. That's all.

  22. #22
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting unique text cells from muliple specific columns?

    I'm afraid regardless of other complexities involved wherever you choose to differentiate / calculate based on formatting VBA is somewhat inevitable*
    (it is predominantly for this reason that it is advised against)

    *unless the formatting is itself determined by logic (eg conditional format rules which can be aped)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1