Query excel data from within the same workbook (Excel 2002)

Topic

New Reply

Hi All!

I’m generally an Access/Word person, but right now I’m desperately trying to work in Excel. Feeling like I have to think backwards. And not being able to search doesn’t help much Can someone point me in a good direction for doing some of the following things?

I am querying a mainframe database and pulling down information from several tables by date into a worksheet. What I’d like to do is be able to have queries from separate sheets in the same book that pull the data for the year from the main worksheet, but I can’t figure out how to query from within the same worksheet.

Once I get the correct information into the correct worksheet (ie. 2002 data in a 2002 worksheet, 2001 in a 2001 worksheet, etc), then I need to transpose the data. In other words, I have a bunch of ratios running across the top of the page, and a bunch of companies running down the left side. I know I can click the paste button to transpose this information to have the ratios running down the left and the companies running across the top (for comparative analysis), but is there an automated way to do this?

The problem is that I’d like to be able to do this on a regular basis, but I’m not quite sure how.

Any pointers, references, or even a suggestion for a good book (although the ones I have here don’t seem to help) would be greatly appreciated.

THANKS!

Reply | Quote

Replies

Those “Excel” queries, do they involve just single tables?

If so, have a look at the following things in Excel:

– Data, filter, autofilter (or advanced filter)
– The Database worksheet functions (DSUM, DCOUNT, DAVERAGE, etcetera)
– Pivot tables (can easily create year-overviews with the year as a page-dropdown)

If the queries are multi-table ones, I suggest doing them outside of Excel, e.g. creating them in the database application itself or to use msQuery to create them.

Reply | Quote

Hi,

Somehow I could not get filter to work. I want to filter my results and put only a certain date into a new worksheet, but can’t seem to accomplish that.

I don’t think a pivot table will work either, I’ve been trying to use that with no luck. I don’t really want to pivot my results. What I want to do is transpose them, so that the companies go across the top and all the ratios (which are field names in the source data) go down the left. I’m able to do this manually, after I’ve manually cut & pasted the data into a separate worksheet.

I’m also not trying to do dsums or anything like that. I just want to present the existing data in a different way.

Here’s what I see in my mind: First sheet, all data, one big table, with ratios for selected companies spanning several years. The ratios go across the top (because they are field names in my source data/mainframe database) and companies go down the left side (because they are records in my source data). Go to next sheet (labelled 2002): I can query the first sheet to just pull that information for year end 2002, then transpose it so that you can see the ratios going down the left side and the companies going across the top, so that you can easily compare a ratio between companies. Next sheet will be 2001, then 2000, etc.

In other words, my mainframe data looks like this:

Name	Report Date	Total Assets	Return on Assets	Net Income
Company A	12/31/2002	500	0.9	45
Company A	12/31/2001	400	1.0	40
Company B	12/31/2002	600	0.8	40
Company B	12/31/2001	500	0.9	45

What I want is:

For 2002:

	Company A	Company B
Total Assets	500	600
Return on Assets	0.9	0.8
Net Income	45	40

(Please don’t check my figures, I just plugged them for an example)

Does this kind of make sense? Is this something you would normally do in Excel?

Reply | Quote

Here is an example of getting your results with a Pivot Table

Steve

Reply | Quote

I had the same Pivot Table in mind. It doesn’t HURT to aggregrate the data, does it? It’s already aggregated. That handy “Page field” lets you select the year to display very nicely.

I’m not really sure what’s going on there with your data and why transposing it would make it easier to read, why the field names are “part of the data”, etc….
Pivots, Autofilter, PasteSpecial…Transpose, =VLOOKUP() or =HLOOKUP() or some combination of the above is all I got for ya.

If comparing the data between companies is the goal, maybe a chart based on the pivot table would really do the trick. You can use data labels or a data table to show actual numbers.

Reply | Quote

You are a GENIUS!

But how the heck did you do it? I can get report date at the top, but I can’t get the field names to go down the left. When I drag things to the data area, it wants to count them, not display the results.

It must be some sort of magic….

Reply | Quote

Steve IS a genius with Excel!

There is no row field in his pivot table. There are three data fields, each set to Sum. But since there is only one value for a given date, the sum *is* the single value.

Reply | Quote

Thank you, Hans. That means a lot coming from you.

Steve

Reply | Quote

And how the heck do YOU have time for it all, Hans? Access, Excel, and wherever else you travel in the course of a day

Thanks again,

Reply | Quote

To see how the table is set up, right click on it, select WIZARD and look at the set up. The one problem is you can NOT name the “fields” to match your the names that are given.

Steve

Reply | Quote

Okay, so then how did you do it….

In the example, the field Return on Assets was added to the data area. In the left column, it says ROA. When I do it, all I get is Sum of Return on Assets.

If I type in the name of the field and then hide the “Sum of” section, then my spreadsheet isn’t really automated, because if a field is added, then all the field names will be off a bit….

Reply | Quote

Look at the spreadsheet that was posted above.

Right click on the Pivot Table…choose Wizard…click Layout button.
See the “Page” field off on its own in the top lefthand side? That’s where you drag the date.

Name should be in the Column area.

Do not put any fields in the Row area. Leave that area empty.

Three fields all go into the Data area. You can double-click on them to change the text that will be used to label them in the resulting chart.

Reply | Quote

Thanks, Dory,

The part I was missing is that you can “alias” the stuff in the middle by double clicking on it!

This is all so cool….

Still, I’m going back to Access

Reply | Quote

Cecilia,

It appears that you are asking for 2 tasks:
A. Select a year (maybe optional)
B. Transpose the row/column into a new table.

I have used your table and data from your earlier posting and pasted them to the attached workbook in a Range (“SourceData”). This is where you would download your data from your query.

Optionally you may select a year.

Then click on the Transpose macro button to transpose your selected data.

Hope this works.
Paul

Reply | Quote

Hey, that’s pretty darned cool! It’s exactly what I was (initially) after…

How did you do the drop down box for the dates? The transpose stuff makes sense to me, I can relate to code

Reply | Quote

It is an AUTOFILTER.

Data – Filter – Autofilter does it.

Steve

Reply | Quote

Doh!

So that’s what that is used for. I was trying to use it all wrong, and it just wasn’t working for me.

How can I thank you????

Reply | Quote

You just did thank us.

Let us know if there are other questions and I’m sure we can find solutions.

Steve

Reply | Quote

[indent]

---

You just did thank us.

---

[/indent]

Nice going, Steve. I was going to suggest a batch of homemade brownies. Oh well…

Reply | Quote

The Drop down arrow for selecting a Date is made using Data > Filter

To have a filter arrow on all fields in a table, select any cell in the table then Data>>Filter>>AutoFilter.

To have arrows on only certain columns, select the field name (top cell) plus a cell(s) under the field name, then Data>>Filter>>AutoFilter.

This allows “filtering out” all but the desired criteria. I believe that with multiple Filter arrows (on different columns), you could first select, say a date, then a “company” or certain ratios.

The AutoFilter is limited in that it only allows you to select exact items which are already present in the record set of the table. For more flexiblity, you can use the Data>>Filter>>AdvancedFilter to select criteria such as “Greater than……(a ratio of 300) or (….Dec 2002 to get all dates after 12/02). Use the table (Range.SourceTable in my example) as your Database range along with a range named Criteria, which has the same field names. Name an Extract range on a new sheet, also with the same field names. Then by entering various criteria under the field names of the Criteria range you can “query” the database much like an Access db with the result set going to the Extract range.

Reply | Quote

AWESOME!

I’m sure this is all really basic stuff for you guys, but it was really hanging me up. Originally, I was trying to use the filter to put stuff on another sheet, but it makes SO much more sense to leave it in place like that. I’m gonna learn this program, if it kills me

How can I thank all you guys?

Reply | Quote

I do something similar. I have a macro import all the data to one worksheet and define a “Range Name” to include the whole pile of data. Then on other pages I have pivot tables that summarize the data, referencing the data by it’s range name. That way the pivots can be updated accurately even if the number of rows in the original data import changes.

Start playing around with the pivot tables and I’m sure you’ll figure them out.

Autofilter is also a good suggestion if you just want to show the original data rows but limited to different criteria. Pivots do COUNT and SUM queries — good for summarizing a big pile of data.

I also use VLOOKUP formulas sometimes to “look up” matching data on other sheets. This is a one-to-one kind of matching.

If you already know Access, I doubt any book will help you out as much as just using online help and doing a few little experiments with these features. I’ll bet you’ll have a solution in no time!

Reply | Quote

Hi Dory,

Pivot tables would be a nice way to automate this, but I’m not quite sure that that’s what I want to do. They seem to aggregate the data, which is not want I want to do. I just want to display the data in a different way.

My big limitation in Access is that I want to use the field names as data. I posted an example above. I figured since Excel doesn’t really have field names–everything is data– then I wouldn’t have this limitation?

Reply | Quote