Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a chart with text not numbers | Charts and Charting in Excel | |||
Making a chart that uses a secondary Y axis | Charts and Charting in Excel | |||
Making chart with hidden Data | Excel Discussion (Misc queries) | |||
Sorting values in a bar chart | Charts and Charting in Excel | |||
Numbers instead of symbols on scatter chart | Charts and Charting in Excel |