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.

Please Login or Register  to view this content.

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

Hi Rylo,

Nice work!

Unfortunately, I think you missed something in your solution...

The original request was to have a dropdown option on the 'Location' field, but it appears to me that your solution provides a 'Grand Summary' of all of the data.

I've been out of the Excel/VB world for while but I was thinking that the only way to achieve the request was to loop through the records by Location,TestID into an array with a count of TestCaseStatus (with a Select Case for all eight status possibilities), then dumping the array to a worksheet and adding the AutoFilter so it can be viewed by Location.

Am I out of my head on this path?

theDude

P.S. - As far as I can tell, the sample total in column K is based on the sum of columns C, D & G...column L is the balance of A, B, E, F, H, I.

dude

I'm struggling to get a firm grip on exactly what the OP is requesting. If there really are a fixed number of test outcomes then build a matrix of the outcomes, and have either all the test ids from the data (hence code to get the unique list), or all the possible test ids (in which case there is no need for code). Use SUMPRODUCT to get the results for the test id / outcome combination.

The drop downs can be easily obtained from an autofilter.

What would really help would be to have a smaller example file, then show the exact outcome for that data......

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.