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.
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.
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
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
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks