+ Reply to Thread
Results 1 to 2 of 2

HELP: Find repeating combos ?

Hybrid View

  1. #1
    Registered User
    Join Date
    06-02-2012
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    95

    HELP: Find repeating combos ?

    I have this table in Excel:


    Column1	Column2	Column3	Column4	Column5	Column6
    50	24	8	4	3	1
    50	18	8	7	1	1
    49	8	7	5	3	1
    49	15	4	3	1	1
    48	15	10	8	4	3
    44	20	5	3	1	1
    43	4	3	3	3	1
    42	16	5	5	1	1
    41	14	10	6	3	3
    41	13	11	8	6	5
    40	9	5	3	2	1
    39	9	7	4	4	3
    39	8	7	6	4	1
    39	9	6	4	3	3
    38	8	7	5	3	2
    38	14	8	4	4	3
    38	17	15	10	8	8
    37	10	10	6	2	2
    37	19	8	4	3	1
    37	23	23	3	2	2
    
    MANY NUMBERS (AT LEAST 1000 ROWS IN THIS TABLE)
    The numbers may repeat in one or more columns.
    It may be something like:
    1 1 1 1 1 1
    OR any combo:
    e.g.:
    23 5 3 1 1 1

    As you maybe note already, numbers are sorted from higher to lower.
    number in Column1 >= number in Column2 >= number in Column3 >= number in Column4 >= number in Column5 >= number in Column6.

    I want a way to find out how many times each combo of six numbers are repeating in this table.
    e.g.
    23 5 3 1 1 1 appears 17 times
    1 1 1 1 1 1 appears 38 times and so on.

    Any help with this, please ?

    Thank you so much !

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: HELP: Find repeating combos ?

    Hello..

    Try using below formula:-

    =SUMPRODUCT(($A$2:$A$21=A2)*($B$2:$B$21=B2)*($C$2:$C$21=C2)*($D$2:$D$21=D2)*($E$2:$E$21=E2)*($F$2:$F$21=F2))

    repeating combo.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com

+ 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