Importing and Analysing xls Files

I have just started using Excel at the office so this is fairly new stuff for me.
My aim is the following - hopefully you can advise me on a good way to approach this.

We have an online system that generates xls dump files. These essentially contain a table with lots of information about every test case we perform. From this table I am interested in three fields: TestID, Location and TestCaseStatus. With these fields I need to generate :

1. A drop down menu to filter by location
2. A table containing the count of each test case status for the relevant location. Every Row represents a TestID and the columns a status.

I was hoping you would be able to give me an idea of a good way to tackle this. My initial thought is that SQL maybe useful to import the desired table columns and then further filtering would have to be done through nested for loops. I'm sure you can suggest a better way though.

Also, if possible, I would prefer the raw data not to be displayed in my analysing spreadsheet - only the summary should be shown.

Any ideas?

To create a filter, use the AutoFilter tool.

To summarize the data, create a Pivot Table based on the data in the download.

Attached is a sample file. Sheet 1 is the download data sample with the AutoFilter applied. Sheet 2 contains a Pivot Table that summarizes the data on Sheet 1.

Hope this helps,
theDude

Hi

I'm not really sure if you want to do these actions on each and every workbook, or if you want to consolidate onto a separate workbook. But here's some starting ideas.

1) The auto filter will give you the drop downs for your raw data. You can then do your filtering by location.
2) You could use the advanced filter to determine a unique list of locations and status. You can then use the SUMPRODUCT function to obtain a count of the combinations.

If you attach an example file showing your structure and some represenatative data, then it would be easier to provide some solution ideas on that example data.


HTH

rylo

Thanks for the reply guys. I should have given a little more information.
I have actually used both the autofilter, to check things manually, and the pivot table to generate what I needed.

There are currently 4 different status in our system - thus four columns are generated. The problem I have, which is why I was told to move away from pivot tables, is that there are actually 8 valid status and hence up to 8 columns. Given that I later use these count values to perform some further calculations, I need the table to retain a fixed format throughout the the life of the project - regardless of our progress.

I thought of adding some dummy values to the raw dump to include all possible status. Ideally, though, this should be a tool that can show our progress at the time of the dump without requiring manual modifications every time.

With that in mind, any further suggestions?

Hi

Add an example file - makes things much easier for us to interpret....


rylo

Hi,

In the attached file the 1st tab, Dump, is the raw data which would be found in a file on its own. The second tab is what I have generated.

The bottom table was generated with a pivot table (it hasn't saved well as I only had access to openoffice). The top table is sample of what I am trying to get, the values completely fictitious. As you can see the pivot table ignores valid status if they are not used in the current dump. However, in the future those additional status values may be used and those that have currently failed will pass, thus removing the failed columns.

I need to have a constant number of columns so that I can then automatically perform the summations included to the right of the 1st table.

I hope that is a bit clearer but I'm not sure it is.

Dan

Dan

Try this.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Analysis")
  OutSH.Range("A5:L" & WorksheetFunction.Max(5, OutSH.Cells(Rows.Count, 1).End(xlUp).Row)).ClearContents
  
  Sheets("dump").Activate
  Range("B:B").AdvancedFilter action:=xlFilterCopy, copytorange:=OutSH.Range("A4"), unique:=True
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  OutSH.Activate
  Range("B5").Formula = "=SUMPRODUCT(--(Dump!$B$2:$B$" & lastrow & "=Analysis!$A5),--(Dump!$S$2:$S$" & lastrow & "=Analysis!B$4))"
  Range("B5").AutoFill Destination:=Range("B5:H5")
  Range("I5").Formula = "=SUMPRODUCT(--(Dump!$B$2:$B$" & lastrow & "=Analysis!$A5),--(Dump!$S$2:$S$" & lastrow & "=""""))"
  Range("B5:I5").AutoFill Destination:=Range("B5:I" & Cells(Rows.Count, 1).End(xlUp).Row)
  
End Sub

I've got absolutely no idea what formulas should go into columns K and L. If you can provide details of what constitute OK and Pending, then I can enhance the program to include those formulas.

rylo

Hey guys,

I went away for the weekend but I'm back now.

I appreciate it's not all that easy to understand so I've uploaded the files I have working currently. The "ML0 export.xls" is a raw dump, the "ML0 planning macro.xls" has all the interesting stuff.

From the latter the Estimates sheet is simply a database of how long we believe each test takes etc. The Times sheet contains the open button where you would load the dump, i.e. "ML0 exports.xls". A pivot table is created with that can be filtered by location. Columns C:G contain the count of each test status per testID. A simple sum is used to compute complete and incomplete columns. The remainder is done with simple lookup tables, but I'm ok with that.

My problem is that as status change in the system it could be that either more columns are added or some removed from the pivot table and thus my sums in columns H and I will no longer be correct.

I don't really know how best to explain it so I hope this makes sense.

Dan

Hi

As you have a fixed number of possible outcomes, add 8 rows to your source data before processing - 1 for each of the possible outcomes. Each row will contain a known input, so it can be removed/hidden from the pivot table. This way, your pivot table will ALWAYS have the same number of columns so your formulas won't be impacted.


rylo

Adding those dummy values is one of the 1st things that came to my mind. What I'm wondering is if there is a way this could be done automatically so that when I give this tool to my manager he doesn't have to fuf around adding dummy values.

Also, if the answer is just add them to the end or start with code; could it be done dynamically to avoid "polluting" the original every time it is loaded in?

Dan

Dan

Looking at the way you have your code, I'd open the data file, add the records, close and save the file, run your pivot table actions, then reopen the data file, delete the records then resave the file.

An alternative to this would be to open the data file, add the records, then save it to a fixed special location. Run your pivot from that copy of the file, and kill it when you have completed.

rylo

Yeah that should do it, good thinking.

Now that I know what to do it should be easier to learn how to do it.

Thanks mate.