Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Counting unique values, 2 columns, alphanumeric

  1. #1
    Join Date
    16th February 2005
    Posts
    3

    Counting unique values, 2 columns, alphanumeric

    I've been all over the map on this one, and really need some help.

    I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows. An example:

    5CZ975 Van
    25RW04 Straight
    5CZ975 Van
    ZJ6026 Van

    So, the result I am after is 2 vans, 1 straight truck

    I found an array in a another post, but it is not working properly (see cell F7 in the attached):

    =sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))
    **ctrl+shift+enter**

    Any help/suggestions would be most appreciated! Thanks in advance.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Couting unique values, 2 columns, alpahnumeric

    Easier than you might've thought...
    Then again, anything is easy if you've done it before right?

    =SUMPRODUCT( 1/COUNTIF($A$2:$A$18,$A$2:$A$18), --($B$2:$B$18=F7) )

    It's a single non-array formula that doesn't need the extra column to do the calc!
    Sub All_Macros(Optional control As Variant)

  3. #3
    Join Date
    16th February 2005
    Posts
    3

    Re: Couting unique values, 2 columns, alpahnumeric

    There I go again, making a mountain out of a molehill! Actually, your solution is the preferred route as I would like to aviod using an array (that would mean I would have to explain what it does ).

    Perfect, thank you so much!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Couting unique values, 2 columns, alpahnumeric

    I edited my original post because I came up with a clean non-array formula solution... you might find it interesting. But... you might have trouble explaining it to the simple folk.
    Sub All_Macros(Optional control As Variant)

  5. #5
    Join Date
    16th February 2005
    Posts
    3

    Re: Couting unique values, 2 columns, alpahnumeric

    I lost them in the first explanation, which was quickly followed by their response, "hey, as long as it works."

    Thanks Aaron!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    3rd January 2007
    Posts
    51

    Re: Couting unique values, 2 columns, alpahnumeric

    Thanks guys. I though for sure I would need VBA coding but this solves my problem nice and easy. This is great. Kudos.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Counting unique values between two columns
    By carg1 in forum EXCEL HELP
    Replies: 5
    Last Post: February 28th, 2012, 05:17
  2. Replies: 2
    Last Post: January 17th, 2007, 03:03
  3. Counting Number Of Unique Values In A Column
    By shevtsov in forum EXCEL HELP
    Replies: 6
    Last Post: December 1st, 2006, 05:26
  4. Using sumproduct for counting unique values
    By thoemmes in forum EXCEL HELP
    Replies: 2
    Last Post: June 29th, 2005, 00:30

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
  •  
porno