# Counting Match Pairs In Rows

1. ## Counting Match Pairs In Rows

I am trying to count the number of matched pairs by rows of data and place this into a chart. Please see the screen shot example and manual solution.

With the data sets:
1-2-3-4
1-3-4-5
2-3-4-6
3-4-5-6

I want to fill out the chart to find the number of times say 1&2 occur in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be filled into the result matrix.

2. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

3. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

4. ## Alternate Solution

Thanks so much for your solution. I countinued to try and solve my issue and came up with another method. Both seem to have their pros and cons.

In my solution, I made another matrix that counts which numbers come up in each data set. Then I did a sumproduct for each pair. See the attached zip spreadsheet.

Thanks again!

5. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

6. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

7. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

8. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

9. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

10. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

11. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

12. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

13. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

14. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

15. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

16. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

17. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

18. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

19. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

20. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

21. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

22. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

23. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

24. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

25. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

26. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

27. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

28. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

29. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

30. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

31. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

32. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

33. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

34. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

35. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

36. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

37. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

38. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

39. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

40. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

41. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

42. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

43. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

44. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

45. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

46. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

47. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

48. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

49. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

50. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

51. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

52. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

53. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

54. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

55. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

56. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

57. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

58. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

59. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

60. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

61. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

62. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

63. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

64. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

65. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

66. ## Re: Counting Match Pairs In Rows

One way which delivers what you're after ..
(with result matrix extended to cover: 0,1,2,3 ... 9 on both axes)

http://www.savefile.com/files/6483947
File: Counting Match Pairs In Rows_bmb2000_wksht.xls

In Sheet1:

Data is in cols B to E, from row3 down

Put in S3:X3

S3: =B3&C3
T3: =B3&D3
U3: =B3&E3
V3: =C3&D3
W3: =C3&E3
X3: =D3&E3

Select S3:X3, copy down as many rows as there are sets of numbers

Put in Z1: '00 (include the leading apostrophe)
Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)
Select Z1:AA1, copy down to AA100

In the matrix, we have the 2 axes
In H2:Q2 are the numbers: 0,1,2,... 9
In G3:G12 are the numbers: 0,1,2,... 9

Put in H3: =INDEX(\$AA:\$AA,MATCH(H\$2&\$G3,\$Z:\$Z,0))
Copy across to Q3, fill down to Q12 to populate the grid

Now to colour the internals of the matrix using conditional formatting

Select H3:Q12
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =ROWS(\$A\$1:A1)<=COLUMNS(\$A\$1:A1)
Format > Patterns tab > Black > OK
Click OK at the main dialog
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com> wrote in
message news:bmb2200.1u8e2r_1124823954.7857@excelforum-nospam.com...
>
> I am trying to count the number of matched pairs by rows of data and
> place this into a chart. Please see the screen shot example and manual
> solution.
>
> With the data sets:
> 1-2-3-4
> 1-3-4-5
> 2-3-4-6
> 3-4-5-6
>
> I want to fill out the chart to find the number of times say 1&2 occur
> in the same set and 1&3, 1&4, 2&3, 2&4, etc. The formula would be
> filled into the result matrix.
>
>
>
> +-------------------------------------------------------------------+
> |Filename: pairs.jpg |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

67. ## Re: Counting Match Pairs In Rows

Oops, correction to formula in line:
> Put in AA1: =COUNTIF(\$S\$3:\$X\$6,Z1)

Put in AA1: =COUNTIF(S:X,Z1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

68. ## Re: Counting Match Pairs In Rows

You're welcome ! Glad to hear that you found a solution to your problem,
and thanks for sharing it with us. Good luck with the game <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bmb2200" <bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com> wrote in
message news:bmb2200.1u9s2k_1124888756.1262@excelforum-nospam.com...
>
> Thanks so much for your solution. I countinued to try and solve my
> issue and came up with another method. Both seem to have their pros
> and cons.
>
> In my solution, I made another matrix that counts which numbers come up
> in each data set. Then I did a sumproduct for each pair. See the
>
> Thanks again!
>
>
> +-------------------------------------------------------------------+
> |Filename: matching pairs.zip |
> +-------------------------------------------------------------------+
>
> --
> bmb2200
> ------------------------------------------------------------------------
> bmb2200's Profile:

http://www.excelforum.com/member.php...o&userid=26560
>

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

#### 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