+ Reply to Thread
Results 1 to 5 of 5

Counting a pair of values

  1. #1
    Registered User
    Join Date
    05-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    56

    Counting a pair of values

    Hi everyone
    I have 6 columns and 1000 Rows populated with numbers from 1 thru 41
    What I'd like to do, is to count all consecutive rows matching same pair and put the results in a 41x41 matrix.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,532

    Re: Counting a pair of values

    what have you got so far? Or are we supposed to mock it up for you?
    follow the advice in the banner at the top of the post and upload a sample that represents what you've got AND what you would want to see.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    05-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Counting a pair of values

    Thank you Sambo Kid trying to help me...for instance In the attached file you can see the number 18 (row 5), was followed by number 5, (row6) .8 times
    all the way down until row 1004, the first one was in rows 5 and 6, the second one in rows 78 79. the last one was found in rows 880 and 881
    the total count for the pair 18-5 was 8, this count will appears in the intersection of column z and row 9 need to do for all pairs (1 1, 1 2, 1 3....41 39, 41 40,
    41 41 filling the matrix 41x41 with the results
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,532

    Re: Counting a pair of values

    it appears to me that a simple adjustment to your formula will get you what you want...
    =SUMPRODUCT(($A$5:$F$1004=I$4)*($A$6:$F$1005=$H5))+SUMPRODUCT(($A$5:$F$1004=I$4)*($B$6:$B$1005=$H5))+SUMPRODUCT(($A$5:$F$1004=I$4)*($D$6:$D$1005=$H5))+SUMPRODUCT(($A$5:$F$1004=I$4)*($E$6:$E$1005=$H5))+SUMPRODUCT(($A$5:$F$1004=I$4)*($F$6:$F$1005=$H5))
    unless I am misunderstanding your requirements.

  5. #5
    Registered User
    Join Date
    05-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Counting a pair of values

    Thank you Mr Sambo KID, making some little adjustment works 100%,I'd like to ask you to finish totally my request, what do I need to do to adjust the range in the
    formula indirectly, for example let insert a column with dates I want to apply the formula to calculate between dates which are in a1 and a2, check the new file..sheet2 is the old file and Dates is the new one with
    little adjustments
    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. Looking for Pair of Values in Same Column - VBA
    By awsexcel123 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-19-2018, 02:57 AM
  2. [SOLVED] Convert Single Pair of Columns to 3 Column (Pair) List
    By kencoburn in forum Excel General
    Replies: 7
    Last Post: 03-04-2017, 01:43 PM
  3. Speeding up a macro that looks for pair of values in long range
    By balth in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2016, 11:16 PM
  4. Replies: 6
    Last Post: 02-04-2014, 12:27 PM
  5. Replies: 0
    Last Post: 04-10-2012, 12:39 PM
  6. Replies: 1
    Last Post: 03-13-2012, 12:56 AM
  7. [SOLVED] etsimate values of a linear trend for each pair of known values
    By Maarten in forum Excel General
    Replies: 2
    Last Post: 01-15-2006, 05:10 PM

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