Creating Line chart, from data every three lines.

A

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Hi,

I'm trying to create a macro to make a line chart. However the buildup of the data creates some problems.

The Data:

CompanyDatePrice 1Price 2
110.09.20141204,991199,77
210.09.20141196,641191,45
310.09.20141196,681191,49
109.09.20141206,381203,29
209.09.20141198,051194,98
309.09.20141198,081195,01
108.09.20141202,831205,30
208.09.20141194,561197,02
308.09.20141194,571197,04
105.09.20141205,301199,49
205.09.20141197,111191,34
305.09.20141197,091191,34
104.09.20141202,601205,42
204.09.20141194,451197,26
304.09.20141194,441197,26
103.09.20141199,371197,59
203.09.20141191,301189,52
303.09.20141191,271189,51
102.09.20141187,201185,29
202.09.20141179,231177,33
302.09.20141179,211177,31
101.09.20141190,041189,27
201.09.20141182,091181,32
301.09.20141182,071181,30

<tbody>
</tbody>


What I want is to make three charts, one for Company 1, 2 and 3.

X-axis will have the Date as the value. and I want Price 1 and 2 to be displayed as a separate lines.

The problem arises when I want to select data from every three lines. I want to make it dynamic aswell, so that if I add new data, I can run the macro again, or it will update itself.

One thing that could help me on the way is either letting me know how to make a line-chart from an array. I populate it like this.

Code: 
    LR1 = Cells(Rows.Count, 1).End(xlUp).Row
    LR = (LR1 / 3) + 1
    
    ReDim XArray(1 To LR, 1 To 2)
    ReDim Yarray(1 To LR)
    x = 1
    
For i = 1 To LR1 Step 3
    XArray(x, 1) = Cells(i, 3).Value
    XArray(x, 2) = Cells(i, 4).Value
    Yarray(x) = Cells(i, 2).Value
    x = x + 1
Next

But how to I create a chart from these values?

another option is this recorded piece:

Code: 
    Range("B1:C30").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Global'!$B$11:$C$41")
    ActiveChart.ChartType = xlLine
    ActiveChart.Axes(xlCategory).Select
    ActiveSheet.ChartObjects("Chart 4").Activate
    Selection.TickLabels.NumberFormat = "dd.mm.åå;@"
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "# ##0_ ;[Red]-# ##0\ "

I could manually select the data I want, but that's a nuisance when the data increase. Is there any way to "loop information to a range"? If so that could be a solution.

Any help appreciated :)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Arithos said:
Hi, that would be preferable, but if you have another solution i'm all ears/eyes :)
Click to expand...
based on what you posted:
CompanyDatePrice 1Price 218Price 1Price 2
110/9/141204.991199.771/9/141190.041189.27
210/9/141196.641191.452/9/141187.21185.29
310/9/141196.681191.493/9/141199.371197.59
19/9/141206.381203.294/9/141202.61205.42
29/9/141198.051194.985/9/141205.31199.49
39/9/141198.081195.018/9/141202.831205.3
18/9/141202.831205.39/9/141206.381203.29
28/9/141194.561197.0210/9/141204.991199.77
38/9/141194.571197.04
15/9/141205.31199.49
25/9/141197.111191.34
35/9/141197.091191.34
14/9/141202.61205.42
24/9/141194.451197.26
34/9/141194.441197.26
13/9/141199.371197.59
23/9/141191.31189.52
33/9/141191.271189.51
12/9/141187.21185.29
22/9/141179.231177.33
32/9/141179.211177.31
11/9/141190.041189.27
21/9/141182.091181.32
31/9/141182.071181.3

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>
Formula in G1 is =COUNTIF(A:A,"="&F1)
Formula in G2 is =IF(ROWS($1:1)>$G$1,"",SMALL(IF($A$2:$A$35=$F$1,$B$2:$B$35),ROWS($1:1))) CSE
Formula in H2 is =IF(ROWS($1:1)>$G$1,"",IFERROR(INDEX(C$2:C$35,MATCH(1,($F$1=$A$2:$A$35)*($G2=$B$2:$B$35),0)),"")) CSE copied down and right.

Such setup to be repeated for cie 2 and 3.
For the chart you could created Named ranges to select ONLY data that is not blank or zero. Such would be dynamic.
 
Upvote 0
cyrilbrd said:
based on what you posted:
CompanyDatePrice 1Price 218Price 1Price 2
110/9/141204.991199.771/9/141190.041189.27
210/9/141196.641191.452/9/141187.21185.29
310/9/141196.681191.493/9/141199.371197.59
19/9/141206.381203.294/9/141202.61205.42
29/9/141198.051194.985/9/141205.31199.49
39/9/141198.081195.018/9/141202.831205.3
18/9/141202.831205.39/9/141206.381203.29
28/9/141194.561197.0210/9/141204.991199.77
38/9/141194.571197.04
15/9/141205.31199.49
25/9/141197.111191.34
35/9/141197.091191.34
14/9/141202.61205.42
24/9/141194.451197.26
34/9/141194.441197.26
13/9/141199.371197.59
23/9/141191.31189.52
33/9/141191.271189.51
12/9/141187.21185.29
22/9/141179.231177.33
32/9/141179.211177.31
11/9/141190.041189.27
21/9/141182.091181.32
31/9/141182.071181.3

<tbody>
</tbody>
Formula in G1 is =COUNTIF(A:A,"="&F1)
Formula in G2 is =IF(ROWS($1:1)>$G$1,"",SMALL(IF($A$2:$A$35=$F$1,$B$2:$B$35),ROWS($1:1))) CSE
Formula in H2 is =IF(ROWS($1:1)>$G$1,"",IFERROR(INDEX(C$2:C$35,MATCH(1,($F$1=$A$2:$A$35)*($G2=$B$2:$B$35),0)),"")) CSE copied down and right.

Such setup to be repeated for cie 2 and 3.
For the chart you could created Named ranges to select ONLY data that is not blank or zero. Such would be dynamic.
Click to expand...


Nicely done :)

Works like a charm.

However, could I not just expand the selection withing the formulas, e.g. C2:C35, to include up to C45? and from that it will change dynamicly?

I'm not sure how to set the named ranges to only select data that is not blank/zero. :S
 
Upvote 0
Arithos said:
Nicely done :)

Works like a charm.

However, could I not just expand the selection withing the formulas, e.g. C2:C35, to include up to C45? and from that it will change dynamicly?

I'm not sure how to set the named ranges to only select data that is not blank/zero. :S
Click to expand...

Yes you could extend range to let's sat row 4000 thus allowing you some room before data would reach this lower limit.

Example of Named Range Given that Tab name is Arithos:
'Date' would refer to =OFFSET(Arithos!$G$1,1,0,COUNTA(Arithos!$G:$G)-1)
'Price_1' would refer to =OFFSET(Arithos!$H$1,1,0,COUNTIF(Arithos!$H:$H,">0"))
'Price_2' would refer to =OFFSET(Arithos!$I$1,1,0,COUNTIF(Arithos!$I:$I,">0"))

now in your chart use the following references:
Y-Values is ='tab_name_here'!Price_1 example ='Arithos'!Price_1
X_axis s ='tab_name_here'!Date
use Price_2 for second field...

Does this make sense to you?
 
Last edited:
Upvote 0
cyrilbrd said:
Yes you could extend range to let's sat row 4000 thus allowing you some room before data would reach this lower limit.

Example of Named Range Given that Tab name is Arithos:
'Date' would refer to =OFFSET(Arithos!$G$1,1,0,COUNTA(Arithos!$G:$G)-1)
'Price_1' would refer to =OFFSET(Arithos!$H$1,1,0,COUNTIF(Arithos!$H:$H,">0"))
'Price_2' would refer to =OFFSET(Arithos!$I$1,1,0,COUNTIF(Arithos!$I:$I,">0"))

now in your chart use the following references:
Y-Values is ='tab_name_here'!Price_1 example ='Arithos'!Price_1
X_axis s ='tab_name_here'!Date
use Price_2 for second field...

Does this make sense to you?
Click to expand...


yes it does, only I will have to amend, taking into account that my table data starts in row 12 in my actual worksheet.


I normally just select a range, then name it, is there a way to input a named range?
 
Last edited:
Upvote 0
to clarify,

how to I make Price_1 refer to =OFFSET(Arithos!$H$1,1,0,COUNTIF(Arithos!$H:$H,">0"))

make a table?
 
Upvote 0
Think I figured it out, I go to the, formula "tab" then define name, then in the section "Refers to:" input the formula, or rather my adjusted version of it do make the named range dynamic.. Am i getting warmer?


Edit:
Yup, figured it out :)

amended like this:
=OFFSET(Arithos!$H$1,11,0,COUNTIF(Arithos!$H:$H,">0"))

Thanks alot

 
Last edited:
Upvote 0
Arithos said:
Think I figured it out, I go to the, formula "tab" then define name, then in the section "Refers to:" input the formula, or rather my adjusted version of it do make the named range dynamic.. Am i getting warmer?


Edit:
Yup, figured it out :)

amended like this:
=OFFSET(Arithos!$H$1,11,0,COUNTIF(Arithos!$H:$H,">0"))

Thanks alot
Click to expand...

You got it...
Sorry for the delay, was on my way home.
Would the solution work for you?

Sent for my phone.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Chart point must move to point of click in chart
Replies
2
Views
58
tigger_na
T
S
  • Question
How do I make a line graph from this data?
Replies
3
Views
204
awoohaw
awoohaw
O
  • Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
307
OrangeJuice
O
T
  • Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
156
tubrak
T
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
262
Alex Blakenburg
A

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top