+ Reply to Thread
Results 1 to 23 of 23

Count unique values across 3 columns

  1. #1
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Count unique values across 3 columns

    Suppose I have the following:

    Col A Col B Col C Result
    1/7/2015 M 1 0
    1/7/2015 M 2 0
    2/7/2015 A 1 1
    2/7/2015 A 1 1
    3/7/2015 C 2 0


    I wish to count the number of occurances where col A and Col B and Col C are the same (this would appear in the result column.)

    I have used countifs(A$2:A$100,A2,B$2:B100,B2,C$2:C100,C2) as array (ctrl+shift+enter) but I have 100,000+ rows and the copy and calculation is extremely slow.

    Does anyone know of a faster way?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    Hi, welcome to the forum

    That does not need to be ARRAY entered, it is a regular excel function, just enter it the normal way.

    Not sure what else you added to that fu7nction, but I don't get the same results you do...
    A
    B
    C
    D
    E
    1
    A B C Result
    2
    1/7/2015 M
    1
    0
    1
    3
    1/7/2015 M
    2
    0
    1
    4
    2/7/2015 A
    1
    1
    2
    5
    2/7/2015 A
    1
    1
    2
    6
    3/7/2015 C
    2
    0
    1

    E2=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)

    Note, you had some of the absoluting ($) a bit wrong, as well
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    Thank you so much

    I did not know it could be entered as a regular formula.

    Is there a faster way that the same result can be achieved? When I copy the forumula down 100,000+ rows, it takes a VERY long time. I have searched everywhere but cannot seem to come up with a solution.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    The calcs should already be a lot quicker, plus, now that is not an ARRAY, it will not need to recalc with every WB change, it will only update when something directly changes it.

    Im curious as to why you need to copy that down for every row though, seems a bit redundant?

    Perhaps a Pivot Table, or a table with formulas showing just the unique values would suite you better?

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    Quote Originally Posted by FDibbins View Post
    now that is not an ARRAY, it will not need to recalc with every WB change, it will only update when something directly changes it.
    You must be thinking of VOLATILE function formulas that recalc with every WB change. Array formulas do not do that unless they contain volatile functions.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    Well I have 100,000+ rows and approx. 50+ columns.

    I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.

    Is there a faster and/or better way this can be achieved?

    I could do a pivot table, but then I need to go back and delete the rows I don't need?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    Quote Originally Posted by Tony Valko View Post
    You must be thinking of VOLATILE function formulas that recalc with every WB change. Array formulas do not do that unless they contain volatile functions.
    Good catch Tony, thanks. It was a bit late, last night.

    Large ARRAY's will still slow things down though

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    That's what it sounds like here.

    When you have "big data" sometimes you just have to accept that things can take some time to complete.

    Sounds like they have 100k formulas each referencing 100k rows so that's gonna take some time!

  9. #9
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    Surely the result can be achieved quicker in another way or with a different formula?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    I have sometimes found that, if I am dealing with historic data - where the answers to formulas will no longer change - it may help to "value" those formulas down a feww '000 rows.

    But, if you are dealing with a really big data base, then maybe you need to consider something like access, instead of excel

    I could do a pivot table, but then I need to go back and delete the rows I don't need?
    Not sure what you mean by that. PT's are seperate tables that will interogate a data base for you, and can be substantially faster than formulas

  11. #11
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    I have sometimes found that, if I am dealing with historic data - where the answers to formulas will no longer change - it may help to "value" those formulas down a feww '000 rows.
    Unfortunately the values will always change depending on parameters so I cannot "value" down the formulas.

    Not sure what you mean by that. PT's are seperate tables that will interogate a data base for you, and can be substantially faster than formulas
    Well you mentioned this earlier

    Perhaps a Pivot Table, or a table with formulas showing just the unique values would suite you better?
    But then I would not be able to delete the rows I don't need would I? The pivot table would show me the unqiue values and where they appear, but that would not achieve the goal of deleting irrelevant data??

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    aahh ok, you did not mention that you wanted to delete rows. Is it time consuming because you need to find the duplicates, or because the calc is taking so long?

  13. #13
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    ok thanks no problem. I thought I had,

    I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.
    I believe it's time consuming as the calc is taking so long but could be finding duplicates as well.

    I have the data sorted by col A then by col B then by col C, so when A and B are the same, the countifs looks for col C to be the same also. I want to keep those rows and delete the others. But the countifs formula down the rows (which will always change, sometimes I could have less) takes a very long time.

    So just looking for some alternative options from others to achieve this result??

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    Can you post a SMALL sample file and show us what results you expect?

    20 rows and a few columns worth of data is all we need.

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: Count unique values across 3 columns

    I must have missed that part, sorry

    For the deleting, have you considered using filters?

    Apply filters, filter on anything that is not 1, and then delete what shows

  16. #16
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    I must have missed that part, sorry
    That's ok

    For the deleting, have you considered using filters?
    Yes I have considered this, however I haven't been able to get to that yet as the formula takes so long

    Can you post a SMALL sample file and show us what results you expect?
    Sure, I have attached it.

    You can see in the example, I would insert the countifs formula in col J, then I would copy it down.

    I would then use VBA to delete the rows I don't need, in this case I would only keep rows 20, 21 and 29, 30.
    Attached Files Attached Files

  17. #17
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    This may be "faster"...

    Enter this formula in J17:

    =A17&" "&B17&" "&C17

    Enter this formula in K17:

    =COUNTIF(J$17:J$35,J17)

    Select J17:K17 and copy down as needed.

    I guess you want to delete the rows where column K=1.

  18. #18
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    This may be "faster"...

    Enter this formula in J17:

    =A17&" "&B17&" "&C17

    Enter this formula in K17:

    =COUNTIF(J$17:J$35,J17)

    Select J17:K17 and copy down as needed.

    I guess you want to delete the rows where column K=1.
    Thanks but unfortunately it was pretty much the same as the original method. Still takes at least 2 mins to calculate

  19. #19
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    Well, I'm out of suggestions.

    2 mins isn't all that bad. I run files that take 20 mins to calculate (but they're doing some heavy duty regression analysis for xray calibration).

  20. #20
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    Well, I'm out of suggestions.
    ok thanks anyway. I am looking for something much quicker but if not then so be it.

  21. #21
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    Maybe post this in the Excel Programming / VBA / Macros sub-forum. Someone might be able to come up with a macro that'll do this.

    Good luck!

  22. #22
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    776

    Re: Count unique values across 3 columns

    Maybe post this in the Excel Programming / VBA / Macros sub-forum. Someone might be able to come up with a macro that'll do this.

    Good luck!
    ok thanks so much! Is there a way I can move this post or do I just submit the question again in the other sub-forum?

  23. #23
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique values across 3 columns

    I would just submit the question again and mention that you have posted this thread and include a link to this thread so folks can see what has already been discussed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  2. [SOLVED] Count unique values only, over two columns in the same row
    By matrixpom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-23-2014, 08:42 AM
  3. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  4. Replies: 2
    Last Post: 01-17-2012, 07:48 AM
  5. Replies: 16
    Last Post: 01-11-2012, 10:51 AM
  6. count unique values in columns based on date values
    By cclntn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2010, 06:16 PM
  7. Count values where unique in both columns
    By krabople in forum Excel General
    Replies: 2
    Last Post: 06-24-2009, 06:08 AM

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