Making a chart from Row of numbers
 

I have a worksheet that has 3 columns and numerous rows with the numbers 1
through 10. Each row has different numbers and I am looking to make a chart
that shows which numbers are in the row together. Any suggestions?

example

Row1 Row2 Row3
1 2 3
4 1 6
2 7 9

Chart:
NUM 1 2 3 4 5 6... etc
1 2 1 1 1 0 1
2 1 2 1 0 0 0
...etc.

Making a chart from Row of numbers
 

Here are a couple suggestions. (One concern with both would be if and
how you would treat a row that had the same number on it twice, ie. 1,
1, 4 or something like that)

I'm assuming that you have the #'s 1 to 10 in cells F3:F12 and in
G2:P2. Put the following formula in G3 and copy down across (This
formula counts duplicates twice, ie. 1,1,3 would produce 3 matches for
1)
=SUMPRODUCT(($A$2:$A$360=$F3)+($B$2:$B$360=$F3)+($ C$2:$C$360=$F3),($A$2:$A$360=G$2)+($B$2:$B$360=G$2 )+($C$2:$C$360=G$2))

If you only want 1,1,3 to produce 1 match for the value 1, you could
use the following formula:
=IF($F3=G$2,COUNTIF($A$2:$C$360,$F3),SUMPRODUCT(($ A$2:$A$360=$F3)+($B$2:$B$360=$F3)+($C$2:$C$360=$F3 ),($A$2:$A$360=G$2)+($B$2:$B$360=G$2)+($C$2:$C$360 =G$2)))

Alternatively, you can use a macro like the following:

Option Explicit

Const SOURCESHEET As String = "Sheet1"
Const DEST As String = "$F$2" 'Same sheet
Const STARTCOL As Long = 1 'First column of data
Const STARTROW As Long = 2 'First row of data

Public Sub Generate()
Dim WS As Worksheet
Dim SrcData As Variant
Dim LastRow As Long
Dim Count(1 To 10, 1 To 10) As Long
Dim i As Long, j As Long
Dim x As Long, y As Long, z As Long

Set WS = Worksheets(SOURCESHEET)
LastRow = WS.Cells(65536, STARTCOL).End(xlUp).Row
SrcData = WS.Range(WS.Cells(STARTROW, STARTCOL), WS.Cells(LastRow,
STARTCOL + 2)).Value

For i = 1 To (LastRow - STARTROW + 1)
x = SrcData(i, 1)
y = SrcData(i, 2)
z = SrcData(i, 3)
Count(x, x) = Count(x, x) + 1
Count(y, y) = Count(y, y) + 1
Count(z, z) = Count(z, z) + 1
Count(y, x) = Count(y, x) + 1
Count(x, y) = Count(y, x)
Count(z, x) = Count(z, x) + 1
Count(x, z) = Count(z, x)
Count(y, z) = Count(y, z) + 1
Count(z, y) = Count(y, z)

Next i

For i = 1 To 10
For j = 1 To 10
WS.Range(DEST).Offset(i, j) = Count(i, j)
Next j
Next i
End Sub

Scott

David wrote:
I have a worksheet that has 3 columns and numerous rows with the numbers 1
through 10. Each row has different numbers and I am looking to make a chart
that shows which numbers are in the row together. Any suggestions?

example

Row1 Row2 Row3
1 2 3
4 1 6
2 7 9

Chart:
NUM 1 2 3 4 5 6... etc
1 2 1 1 1 0 1
2 1 2 1 0 0 0
..etc.

Making a chart from Row of numbers
 

Here are a couple suggestions. (One concern with both would be if and
how you would treat a row that had the same number on it twice, ie. 1,
1, 4 or something like that)

I'm assuming that you have the #'s 1 to 10 in cells F3:F12 and in
G2:P2. Put the following formula in G3 and copy down across (This
formula counts duplicates twice, ie. 1,1,3 would produce 3 matches for
1)
=SUMPRODUCT(($A$2:$A$360=$F3)+($B$2:$B$360=$F3)+($ C$2:$C$360=$F3),($A$2:$A$360=G$2)+($B$2:$B$360=G$2 )+($C$2:$C$360=G$2))

If you only want 1,1,3 to produce 1 match for the value 1, you could
use the following formula:
=IF($F3=G$2,COUNTIF($A$2:$C$360,$F3),SUMPRODUCT(($ A$2:$A$360=$F3)+($B$2:$B$360=$F3)+($C$2:$C$360=$F3 ),($A$2:$A$360=G$2)+($B$2:$B$360=G$2)+($C$2:$C$360 =G$2)))

Alternatively, you can use a macro like the following:

Option Explicit

Const SOURCESHEET As String = "Sheet1"
Const DEST As String = "$F$2" 'Same sheet
Const STARTCOL As Long = 1 'First column of data
Const STARTROW As Long = 2 'First row of data

Public Sub Generate()
Dim WS As Worksheet
Dim SrcData As Variant
Dim LastRow As Long
Dim Count(1 To 10, 1 To 10) As Long
Dim i As Long, j As Long
Dim x As Long, y As Long, z As Long

Set WS = Worksheets(SOURCESHEET)
LastRow = WS.Cells(65536, STARTCOL).End(xlUp).Row
SrcData = WS.Range(WS.Cells(STARTROW, STARTCOL), WS.Cells(LastRow,
STARTCOL + 2)).Value

For i = 1 To (LastRow - STARTROW + 1)
x = SrcData(i, 1)
y = SrcData(i, 2)
z = SrcData(i, 3)
Count(x, x) = Count(x, x) + 1
Count(y, y) = Count(y, y) + 1
Count(z, z) = Count(z, z) + 1
Count(y, x) = Count(y, x) + 1
Count(x, y) = Count(y, x)
Count(z, x) = Count(z, x) + 1
Count(x, z) = Count(z, x)
Count(y, z) = Count(y, z) + 1
Count(z, y) = Count(y, z)

Next i

For i = 1 To 10
For j = 1 To 10
WS.Range(DEST).Offset(i, j) = Count(i, j)
Next j
Next i
End Sub

Scott

David wrote:
I have a worksheet that has 3 columns and numerous rows with the numbers 1
through 10. Each row has different numbers and I am looking to make a chart
that shows which numbers are in the row together. Any suggestions?

example

Row1 Row2 Row3
1 2 3
4 1 6
2 7 9

Chart:
NUM 1 2 3 4 5 6... etc
1 2 1 1 1 0 1
2 1 2 1 0 0 0
..etc.