How to make comparison chart to display two varying sets of data.

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Matrix2021
    New Member
    • Jul 2020
    • 13
    #1

    How to make comparison chart to display two varying sets of data.

    Hello.

    I am not sure if you can help me. I just don't know...

    I would love to know, how to make comparison chart to display two varying sets of data.

    I have two workbooks.

    One of the workbook have 181 worksheets. "Each worksheet name after a country and have its own data.

    Next

    The other workbook have 185 worksheets."Eac h worksheet name after a country and have its own data.

    This is the hard part:

    #1 How can I link both workbook data together using macros? Country vs Country or USA vs USA as an example.

    #2 How can I create a comparison chart to display two varying sets of data, both varying sets of data beginning with the zero values?

    If you think I sound crazy. Please just say so lol. I am not sure if you are open in helping me. If so, I am looking for someone to point me to the right direction.

    Thanks in advance

    Cheers,


    https://drive.google.co m/file/d/1mmsL54lWBSVNsr P0INleJe-uW8U-2VVD/view?usp=sharin g


    https://drive.google.co m/file/d/1JigNJKkY2IMGg4-aSXu0NnFnRRuvb6 jx/view?usp=sharin g
    Tags: None
    • SioSio
      Contributor
      • Dec 2019
      • 272
      #2
      In my environment I can't see the link you provided, so it may be an irrelevant answer.

      First, prepare two excel sheets(Ex. sheet1 and sheet2).
      prepare two combobox and two commandbutton in the userform.
      The two combobox store the sheet names of the two books In UserForm_Initia lize() function.
      Select an item from each of the two combobox.
      Click the first button to link Sheet1 and Sheet2 with the sheets of the two books selected in the combobox in CommandButton1_ Click() function.
      Click the second button to display a scatter plot of the data on the two sheets in CommandButton2_ Click() function..
      Modify data range used to draw the graph, graph type and each Const data for your envronment.

      Code:
      Dim SheetA As String
Dim SheetB As String
Const Path As String = "C:\Excel_data\"
Const BookA_fname = "BookA.xlsx"
Const BookB_fname = "BookB.xlsx"
Const BookA As String = Path & BookA_fname
Const BookB As String = Path & BookB_fname

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=BookA
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=BookB
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub

Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub

Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub

Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub

Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

        Comment

        • Matrix2021
          New Member
          • Jul 2020
          • 13
          #3
          Hello SioSio.

          I am not sure if I can fix this. I am trying my best to follow your teaching. I am not sure what I have done is correct and it seems like I am not getting the results.

          Please SioSio. If you are willing please let me know as I have place the codes in the files below.

          Let me know, how to move forward

          This is a short video that explain my problem: https://drive.google.com/file/d/1u6d...ew?usp=sharing

          Thanks in advance

          Unemployment_Rate.xlsm
          https://drive.google.com/file/d/1qLWLCdMvyurnP0AHRTSCiypwHKutzY7B/view?usp=sharing


          GDP_Annual_Growth_Rate_%.xlsm
          https://drive.google.com/file/d/1mtED7Bx6nHo0AAxfGS7wD0QoVW0NWsfq/view?usp=sharing

            Comment

            • SioSio
              Contributor
              • Dec 2019
              • 272
              #4
              It seems that the contents I wrote before did not reach you well, so I will show you the procedure again.

              1. Create a "new" excel book.

              2.Prepare two excel sheets(Ex.sheet 1 and sheet2).

              3.developter tab
              Create a Developer tab for your convenience. To turn on the Developter tab, execute the following steps.
              3.1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
              3.2.Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
              3.3.Check the Developer check box.
              3.4.Click OK.
              3.5. You can find the Developer tab next to the View tab.

              4. Click "Visual Basic" on the Developer tab to open the vba editor.

              5.Click Insert, "User form".If the Toolbox does not appear automatically, click View, Toolbox.

              After this, create two comboboxes and two buttons according to the contents written earlier.

              P.S.
              The link you posted is not visible in my environment.
              Please use the tools in "Post your reply".

                Comment

                • Matrix2021
                  New Member
                  • Jul 2020
                  • 13
                  #5
                  SioSio

                  Thanks for your feedback. I greatly appreciate it.
                  I have followed your second comments, but when it comes to the coding. I am not sure if I made a mistake. I have receive an error which states: "Compile error: Invalid qualifier".

                  Please view code and image for a better understanding.

                  Thanks in advance for your swift reply.


                  Code:
                  Private Sub UserForm_Click()

End Sub
Dim SheetA As String
Dim SheetB As String
Const Path As String = "C:\Excel_data\"
Const BookA_fname = "C:\Users\Corey\Desktop\Unemployment_Rate"
Const BookB_fname = "C:\Users\Corey\Desktop\GDP_Annual_Growth_Rate_%"
Const BookA As String = Path & BookA_fname
Const BookB As String = Path & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=Unemployment_Rate.xlsx
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).Value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).Value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub
                  Attached Files

                    Comment

                    • SioSio
                      Contributor
                      • Dec 2019
                      • 272
                      #6
                      1. Delete this line.
                      Code:
                      Private Sub UserForm_Click()
 
End Sub
                      2. Specify the following character string for the constant.
                      Code:
                      'Specify the path of Book A.
'(Ex.)
'Const Path1 As String = "C:\Excel_data1\"
Const Path1 As String = "Your excel Book A path\"

'Specify the path of Book B.
'(Ex.)
'Const Path2 As String = "C:\Excel_data2\"
Const Path2 As String = "Your excel Book B path\"

'Specify the file name of Book A
'(Ex.)
'Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookA_fname As String = "Unemployment_Rate.xlsx"

'Specify the file name of Book B
'(Ex.)
'Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"

'Specify the full path of Book A. No need to change.
Const BookA As String = Path1 & BookA_fname

'Specify the full path of Book B. No need to change.
Const BookB As String = Path2 & BookB_fname
                      3. In the Sub UserForm_Initia lize() subroutine, restore the following.(No need to change.)
                      Code:
                      	Workbooks.Open Filename:=Unemployment_Rate.xlsx
		'｜
		 'V
	Workbooks.Open Filename:=BookA
	Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
		'｜
		 'V
	Workbooks.Open Filename:=BookB
                      4.By changing the constant, change the variable "path" of Private Sub CommandButton2_ Click () to "path1" and "path2".
                      Code:
                      Private Sub CommandButton2_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path1 & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path2 & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
    
End Sub

                      In the example I showed, It put a link of A1:B5 of BookA to Sheet1 and a link of A1:B5 of BookB to Sheet2, and draw a scatter plot of them.
                      Change the variables i1 and i2, j1 and j2 of Private Sub CommandButton1_ Click () and change the ange ("A1: B5") of Private Sub CommandButton2_ Click () according to your environment.

                        Comment

                        • Matrix2021
                          New Member
                          • Jul 2020
                          • 13
                          #7
                          SioSio. I must say to you again... Thank you for your patience and support.

                          I have followed your instructions and tuition. Please identify below if the code have been plugin into the right location and please view the screen shot of the error and other photos that supports what I am trying to accomplish.

                          Thanks in advance SioSio.

                          Click image for larger version Name: 1.jpg Views: 1 Size: 94.1 KB ID: 5415013
                          Click image for larger version Name: 2.jpg Views: 1 Size: 38.0 KB ID: 5415014
                          Click image for larger version Name: 3.jpg Views: 1 Size: 188.2 KB ID: 5415015
                          Click image for larger version Name: 4.jpg Views: 1 Size: 122.9 KB ID: 5415016
                          Click image for larger version Name: 5.jpg Views: 1 Size: 68.8 KB ID: 5415017

                          Code:
                          Dim SheetA As String
Dim SheetB As String
'Specify the path of Book A.
'(Ex.)
'Const Path1 As String = "C:\Excel_data1\"
Const Path1 As String = "Your excel Book A path\"
 
'Specify the path of Book B.
'(Ex.)
'Const Path2 As String = "C:\Excel_data2\"
Const Path2 As String = "Your excel Book B path\"
 
'Specify the file name of Book A
'(Ex.)
'Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookA_fname As String = "Unemployment_Rate.xlsx"
 
'Specify the file name of Book B
'(Ex.)
'Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
 
'Specify the full path of Book A. No need to change.
Const BookA As String = Path1 & BookA_fname
 
'Specify the full path of Book B. No need to change.
Const BookB As String = Path2 & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
        Workbooks.Open Filename:=Unemployment_Rate.xlsx
        '|
         'V
    Workbooks.Open Filename:=BookA
    Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
        '|
         'V
    Workbooks.Open Filename:=BookB
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).Value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).Value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

                            Comment

                            • SioSio
                              Contributor
                              • Dec 2019
                              • 272
                              #8
                              The base code is shown in #2.
                              #6 shows only the code in #2 and the modified portion of your code shown in #5.
                              Code:
                              Dim SheetA As String
Dim SheetB As String
Const Path1 As String = "C:\Users\Corey\Desktop\"
Const Path2 As String = "C:\Users\Corey\Desktop\"
Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookA As String = Path1 & BookA_fname
Const BookB As String = Path2 & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=BookA
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=BookB
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path1 & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path2 & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

                                Comment

                                • Matrix2021
                                  New Member
                                  • Jul 2020
                                  • 13
                                  #9
                                  SioSio.

                                  Hello,

                                  The VBA coding is great. You have given me an other way to get similar results and I am thankful for the opportunity.

                                  Will chat soon.

                                  Thanks for taking the time in helping me move to the next level.

                                  Greatly appreciate it.

                                  Solve

                                    Comment

                                    Previous template Next
                                    Working...