+ Reply to Thread
Results 1 to 4 of 4

Count the number combinations present if the order doesn't matter

  1. #1
    Registered User
    Join Date
    02-28-2015
    Location
    Birmingham, England
    MS-Off Ver
    2013
    Posts
    5

    Count the number combinations present if the order doesn't matter

    Count the number combinations present if the order doesn't matter (using pivot tables)
    If I had column A and column B:

    A B
    red green
    green red
    red green
    blue pink
    pink blue
    blue pink
    blue pink
    black white
    black white
    white black

    Let's say I have hundreds of rows of combinations. What I need to do is on a second sheet, show all the different combinations and the number of times each occurs. So for the above, the result would be:

    Combination: Number of times:
    red green 3 (whether it is green first or red first doesn't matter and likewise for the others)
    blue pink 4
    black white 3

    So, I would need it to give me the combination and the number of times it occurs. Any idea how I could do this? Ideally using pivot tables...

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    Re: Count the number combinations present if the order doesn't matter

    Not sure it can be done with Pivot tables

    You could put this into Column C copied down or something similar in a table with known combinations

    =COUNTIFS($A$1:$A$10,A1, $B$1:$B$10,B1)+COUNTIFS($A$1:$A$10,B1, $B$1:$B$10,A1)
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-28-2015
    Location
    Birmingham, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Count the number combinations present if the order doesn't matter

    Thanks, Chemist B.

    My problem with this is I dont have a list of known combinations. I need to determine both the combinations I have and the number of times each occurs (again irrelevant of the order that they are in). Any further suggestions would be greatly appreciated.

    BOY101

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    Re: Count the number combinations present if the order doesn't matter

    Okay, I did this in two steps

    In E2 copied down
    =IF(AND(COUNTIF($E1:$E$1,A2)+COUNTIF($F1:$F$1,A2), COUNTIF($E1:$E$1,B2)+COUNTIF($F1:$F$1,B2)),"",A2)
    In F2 copied down
    =IF(AND(COUNTIF($E1:$E$1,B2)+COUNTIF($F1:$F$1,B2), COUNTIF($E1:$E$1,A2)+COUNTIF($F1:$F$1,A2)),"",B2)
    E and F can be hidden once you create them.

    In H2 as an ARRAY Function copied down
    =IFERROR(INDEX($E$2:$E$11, SMALL(IF(LEN($E$2:$E$11)>0, ROW($E$2:$E$11)-ROW($E$2)+1),ROWS($A$1:$A1))),"")
    I2
    =IFERROR(INDEX($F$2:$F$11, SMALL(IF(LEN($F$2:$F$11)>0, ROW($F$2:$F$11)-ROW($F$2)+1),ROWS($A$1:$A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    and lastly in J2
    =COUNTIFS($A$2:$A$11,$H2, $B$2:$B$11,$I2)+COUNTIFS($A$2:$A$11,$I2, $B$2:$B$11,$H2)
    Attached Files Attached Files

+ 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. Replies: 0
    Last Post: 03-03-2015, 11:43 AM
  2. How to generate combinations when repeating is OK and order doesn't matter
    By toddah in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2014, 01:49 PM
  3. [SOLVED] Lookup and count number of times a number is present???
    By greenfox74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-31-2012, 12:21 PM
  4. Generate combinations for a variable number of columns in order
    By q83 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2011, 04:17 PM
  5. filtering rows BUT order of columns does not matter
    By jaronimo in forum Excel General
    Replies: 5
    Last Post: 03-16-2011, 07:45 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