Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Importing several excel files into one to create a bar graph from all the data

    Hi

    I'm new here but I've been self learning excel for some time, although I still know very little!

    I'm wanting to do something, but don't really know how so I am hoping someone will be kind enough to help.
    I have an excel spreadsheet, which is essentially a list of questions in one collumn, and either a pass or fail (technically there is a pass, fail, fail) option from a selection field in the 2nd collumn.

    I use these to create a "report" as part of my work. Each site I visit, a report is created.

    What I am wanting to do, is import all of these excel workbooks into a single workbook, with the data from them all on one worksheet, and a bar graph on the 2nd sheet. The bargraph should show all the questions along the horizontal axis, and then 3 bars for each question that relates to the selection (pass, fail, fail) from the original workbooks.

    I hope that makes sense, and I hope someone can help!

    Kind Regards
    Darren
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I don't see how the att workbook explains what you want. Provide an example of your source workbook(s) and an example of how it would look on the destination sheet.

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks forresponding dguillett. The attached is a copy of the workbook that I am using. I currently have circa 30 copies of the attached from different sites that I'd like to compile into a single workbook and then compare the data by graphing.

    Hope that better explains things.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Not really. Read my reply again.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Ryu,

    Are you looking for something like this?

    BarGraph1.png

    BarGraph2.png

    HTH,
    Maud
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    ryu1200 (2014-10-03)

  7. #6
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Maud the output looks exactly like what I was wanting.

    I can't check the attachments yet. Only got access to my phone until I check in to my hotel tonight but I'll be sure to come back to you if I've got any issues.

    Thanks again.

  8. #7
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud

    Sorry it's taken me a bit longer than intended to come back to you.

    The outputted graph is exactly what I want, but is there an easy way to import all the data held on all the workbooks I have into the one worksheet for the graph to pickup all the data?

    Each store visit, a new workbook is created, so I have a folder full of workbooks, all identical in layout.

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Sure, post a sample workbook and answer theses questions:
    1. Will all the workbooks always be in the same folder and will there be any other files in the folder besides them?
    2. Is there a scheme to the names of the workbooks whose data needs to be imported?
    3. Are the questions and answers the only data to be transferred?
    4. Will the data from the individual workbooks be transferred to the master workbook all at the same time or individually as they are created?

    As the data is imported to the master, the graph will auto update.

    Thanks,
    Maud

  10. #9
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud

    the file I attached above is exactly the same as the workbook's Im working with.

    To answer the questions:

    1. They will always live in the same folder, and no other files besides them.
    2. There is a naming scheme that I use <store name> Inspection <inspection date)
    3. Exactly, in fact the questions are set, and will never change, so it's only really the answers that are needed.
    4. I don't think it matters, whatever is best... If I can load them all at once, when I need to re-generate the graph that would be good. I can then tweak what data it uses. for example, I could remove the workbooks for the stores that don't belong in a certain geographical area, and product a graph just for an area, and then compare against another area if you see what I mean.

    Kind Regards
    Darren

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    The questions were well answered. Thank you. Could you please give me an example of a workbook name <store name> Inspection <inspection date> so I can see if there are spaces or special characters? I will adjust the code that will allow you to filer out any store or geographical area from. The store I can retrieve from the name. Is there s field that can be added to the workbooks that may gather data as to their geographical location? If so, than you would not have to selectively remove the workbooks

    Thanks,
    Maud

  12. #11
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    An example would be:

    "Manchester Inspection 02 10 14"

    I'm afraid there's nothing in the naming to give away the area etc (We essentially split the country into 2 regions, which are then devided into areas) But if I can just select which stores to graph that would be fantastic.

    Thank you so much for your help thus far Maud.

    Kind Regards
    Darren

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    ryu.

    Here is the completed workbook. Click the Get Data Button on the sheet to open the Open dialogue box. It will open to the folder that the master Inspection copy is in so it would save navigation time if you placed all the inspection workbooks there as well. Select the file and open it. The data will automatically import to the next line. You will notice that each inspection is scored. On Sheet2, the graph will be updated to reflect the new addition. From the Inspection sheet, you can apply a filter to the name to view only the inspections you wish to see. The graph will update by graphing only those filtered inspections. Please be careful not to erase/overwrite formulas, change sheet or Workbook names, or add/delete rows or columns. Make sure that you keep that naming format of your sheets as

    Name Inspection Date

    If the name has a space then condense it. Example: Change Palm Bay Inspection 2 13 2014.xlsx to PalmBay Inspection 2 13 2014.xlsx

    This will enable the name to be properly displayed on the Inspection Sheet

    This was another fun project. Thanks!

    Maud

    Unfiltered:
    Inspection1.png

    Unfiltered:
    Inspection2.png

    Filtered:
    Inspection3.png

    Filtered:
    Inspection4.png
    Attached Files Attached Files

  14. #13
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud

    Thanks for all your good work, I've got a couple of issues though...

    1. The colour formatting doesn't work after row 19, which I can probably fix with conditional formatting?
    2. When I delete out your test lines, the graph updates correctly, i.e the data is removed from the graph, but when I start to load in the correct data, the graph no longer updates?

    Kind Regards
    Darren

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Please be careful not to erase/overwrite formulas, change sheet or Workbook names, or add/delete rows or columns.
    ryu,

    Great beta testing!

    I added a button to delete rows in a way that will not disrupt the formulas and their ranges in rows 1000-1003:

    =SUMPRODUCT(--(F$1:F$1000="Good standards or Issues resolved"),--(SUBTOTAL(3,OFFSET(F$1,ROW(F$1:F$1000)-MIN(ROW(F$1:F$1000)),,))))

    These formula control the data sent to the graph. If you delete rows as you did, the formulas will not have corresponding ranges.

    To delete rows, you must select the name of the inspection in column A. It will not let you delete the header row or any row below the last row with data. While I was at it, I filled the format conditions for the rest of the rows and added error handling if you clicked cancel in the open file dialogue box. Below is the revised file.

    Code:
    Private Sub CommandButton2_Click()
    '------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim Row As Integer
        Dim Msg, Style, Title, Help, Ctxt, Response, MyString
        Row = ActiveCell.Row
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    '------------------------------------------------
    'TEST IF MORE THAN ONE CELL IS SELECTED
        If Selection.Count > 1 Then
            MsgBox "Please select only one inspection from column A"
            Exit Sub
        End If
    '------------------------------------------------
    'TEST IF INSPECTION (COL A) IS SELECTED
        If ActiveCell.Column <> 1 Then Exit Sub
        If Row = 1 Then
            MsgBox "You cannot delete the header row"
            Exit Sub
        End If
    '------------------------------------------------
    'TEST IF HEADER ROW IS SELECTED
        If Row > LastRow Then
            MsgBox "There is nothing to delete in row " & Row
            Exit Sub
        End If
    '------------------------------------------------
    'CONFIRM ROW DELETION
        Msg = "Are you sure you want to delete row " & Row & "?"
        Style = vbYesNo + vbExclamation + vbDefaultButton2
        Title = "Delete Row"    ' Define title.
        Response = MsgBox(Msg, Style, Title)
    '------------------------------------------------
    'DELETE AND REPLACE ROW
        If Response = vbYes Then
            ActiveSheet.Rows(Row).Delete
            ActiveSheet.Rows(999).Copy
            ActiveSheet.Rows(LastRow).Select
            Selection.Insert Shift:=xlDown
            Application.CutCopyMode = False
            [a1].Select
        Else
            MsgBox "No records were removed."
        End If
    End Sub
    Let me know if any other adjustments need to be made.
    Maud
    Attached Files Attached Files

  16. #15
    New Lounger
    Join Date
    Sep 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Brilliant Maud, I bow to your skills :-)

    One thing though.... On the graph tab, the question:

    "Are all fire exit signs clearly visible and exit routes / extinguishers and doors unobstructed? "

    The data is all there, but the text isn't... It's got to be something local because it seems to display fine in the PNG images you posted above, but I've no idea whats causing it?

    I'll play some more with it today and see if it fits the needs I'm looking for, but so far so very good, thanks again Maud.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •