Excel Help

[PassiveTheory]

So I'm trying to finish my final for my database class except I can't seem to accomplish what I want to do.

Here's the skinny:

I have 10,000 entries of data in one column, each entry consisting of a number 0-5, and in the next column over I want to indicate which of the entries in the column on the left are a "2" or a "3" (so, no, I can't just go through the whole fucking list typing in stuff to indicate which ones are 2's or 3's) so that I can proceed to filter out only the entries of 2's and 3's.

So how do I do this shit?

[sydaud]

Data - Filter -Auto Filter
Use the pull down.....

[anti-square]

Yup. Beat me to it.

[PassiveTheory]

Thank you!

[sydaud]

Looks like somebody missed a day of class, ha ha!

[PassiveTheory]

Ugh, no. I just absolutely hate the teacher. Everything he says just goes over my head because of how much of a dick he is.

Don't suppose you guys are good with Access? Because, basically, here's my new problem: Does anyone know how to make filters permanent on Access? No matter how many times I save my queries with the filter on, whenever I re-open the query it reverts back to normal. And that's something I know that asshole didn't teach us...

[real talk]

Don't you have a god damned textbook?

[Hannahrain]

Everything he says just goes over my head because of how much of a dick he is.

There's no such thing as not understanding someone's words because they're a bad person. What a load of crap. You either comprehend or you don't.

If he can't understand this because it's not well-meaning, can someone spell it out for him? Thanks in advance.

[menikmati]

I'm going to assume it's Access 2007. Open up your table in DesignView, then right click and goto properties. The property box should open up, and in the filter box you can then enter what sort of filters you want (example: "Month = 'June'")...you can use more than one filter obviously. Then once you enter the filter(s) that you want, just make sure the 'Filter On Load' box is turned onto "Yes", and then save and you should be good to go.

[PassiveTheory]

Thanks, Erik

[PlayaDelWes]

Calling all Excel (2007) experts!!! Things would be so much more efficient if I could figure out the following:

I use GETPIVOTDATA formulas (not shown) to pull data from pivots such as the one below into pretty reports. My reports have views for both summary and the four or so detail breakouts that roll-up. Currently, I have to link to two separate pivots and thus need two separate GETPIVOTDATA formulas.

What I would like to do is have one pivot that shows the detail, but also rolls up at a summary level identical to the way the detail is broken out. Basically, all I want is to break out the “Grand Total” line using the same Row Labels as I have up in the detail. Anyone???

[fatbastard]

It doesn't look as pretty, but if you go into your pivot table field list, click on the weekend field drop down, select field settings, select custom, then select sum, then click OK. It will give you what you want.

[MoSetsfire]

Calling all Excel (2007) experts!!! Things would be so much more efficient if I could figure out the following:

I use GETPIVOTDATA formulas (not shown) to pull data from pivots such as the one below into pretty reports. My reports have views for both summary and the four or so detail breakouts that roll-up. Currently, I have to link to two separate pivots and thus need two separate GETPIVOTDATA formulas.

What I would like to do is have one pivot that shows the detail, but also rolls up at a summary level identical to the way the detail is broken out. Basically, all I want is to break out the “Grand Total” line using the same Row Labels as I have up in the detail. Anyone???

]

Maybe im oversimplifying this, but if youre not using the actual pivot table to present why not just use a sum/sumproduct/sumif formula at for the grand total line within your presentation?

[PlayaDelWes]

It doesn't look as pretty, but if you go into your pivot table field list, click on the weekend field drop down, select field settings, select custom, then select sum, then click OK. It will give you what you want.

Thanks. This is essentially what I did. The problem is that when you sum a subgroup, it eliminates one pair of field / item qualifiers in the GETPIVOTDATA formula, so you can’t apply the same formula across the entire sheet if you are going to switch between showing summary and detail.

=IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal!$B $5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$4, "StateGroup",$F$8), 0)

When summarizing data, needs to then become (notice StateGroup is no longer there):

=IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal!$B $5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$4 )

To work around that, I’m repeating the formula twice (once with 4 qualifiers and once with 5 qualifiers) with an IF statement built referencing a cell with a drop-down of StateGroup selections where I also added “Total Population” as one of the selections in the drop-down.

=IF($F$8="Total Population",IFERROR(GETPIVOTDATA("ItemNum",PivotSt ateTotal!$B$5,"CurFlag",$C24,"EventCd",$B24,"Month Year",AH$4), 0),IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal! $B$5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$ 4,"StateGroup",$F$8), 0))

Maybe im oversimplifying this, but if youre not using the actual pivot table to present why not just use a sum/sumproduct/sumif formula at for the grand total line within your presentation?

It’s because I’m either showing the summary OR one of the StateGroup selections, not all at the same time with a sum at the bottom. This thing has been changing so much, it’s important for me to have the same formula throughout the entire sheet and no references to other sheets (except for the ones the pivots are on).

[Courtney]

I had coffee with a guy from a major tech firm recently about their jobs for mbas, and asked him what sort of skills were the ones he uses the most from school. Without blinking, he said: "PIVOT TABLES."

I should probably learn to do this.

[ThatGirl]

I had coffee with a guy from a major tech firm recently about their jobs for mbas, and asked him what sort of skills were the ones he uses the most from school. Without blinking, he said: "PIVOT TABLES."

I should probably learn to do this.

This is true Court, anytime I recruit someone that has strong Excel skills, pivot tables are part of the requirement. But I have no idea what they do.

[MoSetsfire]

Thanks. This is essentially what I did. The problem is that when you sum a subgroup, it eliminates one pair of field / item qualifiers in the GETPIVOTDATA formula, so you can’t apply the same formula across the entire sheet if you are going to switch between showing summary and detail.

=IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal!$B $5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$4, "StateGroup",$F$8), 0)

When summarizing data, needs to then become (notice StateGroup is no longer there):

=IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal!$B $5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$4 )

To work around that, I’m repeating the formula twice (once with 4 qualifiers and once with 5 qualifiers) with an IF statement built referencing a cell with a drop-down of StateGroup selections where I also added “Total Population” as one of the selections in the drop-down.

=IF($F$8="Total Population",IFERROR(GETPIVOTDATA("ItemNum",PivotSt ateTotal!$B$5,"CurFlag",$C24,"EventCd",$B24,"Month Year",AH$4), 0),IFERROR(GETPIVOTDATA("ItemNum",PivotStateTotal! $B$5,"CurFlag",$C24,"EventCd",$B24,"MonthYear",AH$ 4,"StateGroup",$F$8), 0))

It’s because I’m either showing the summary OR one of the StateGroup selections, not all at the same time with a sum at the bottom. This thing has been changing so much, it’s important for me to have the same formula throughout the entire sheet and no references to other sheets (except for the ones the pivots are on).

I essentially do the same thing


IFERROR(IF($D6="",IF($E6="",GETPIVOTDATA("Amount", Pivot!$A$12,"Client",$F6,"Period",G$3,"Type",$A6," Category",$B6),GETPIVOTDATA("Amount",Pivot!$A$12," Client",$F6,"Period",G$3,"Type",$A6,"Category",$B6 ,"Client_Execution",$E6)),GETPIVOTDATA("Amount",Pi vot!$A$12,"Client",$F6,"Period",G$3,"Type",$A6,"Ca tegory",$B6,"Client_Execution",$E6,"Cust",$D6)),0)

If you find a more creative solution let me know, you've peeked my interest

[MoSetsfire]

You could also skip the pivot table all together if you have the data sets and used named ranges/sumproduct to achieve similar results.

[PlayaDelWes]

I had coffee with a guy from a major tech firm recently about their jobs for mbas, and asked him what sort of skills were the ones he uses the most from school. Without blinking, he said: "PIVOT TABLES."

I should probably learn to do this.

Courtney, the best way to learn this stuff is plagiarism, reverse engineering, and experimenting on your own. Excel 2007 has a finite set of properties and settings for PivotTables and by tinkering around, it’s not hard to understand what each does. The tough part is finding an interesting data set you can use as a playground.

I essentially do the same thing


IFERROR(IF($D6="",IF($E6="",GETPIVOTDATA("Amount", Pivot!$A$12,"Client",$F6,"Period",G$3,"Type",$A6," Category",$B6),GETPIVOTDATA("Amount",Pivot!$A$12," Client",$F6,"Period",G$3,"Type",$A6,"Category",$B6 ,"Client_Execution",$E6)),GETPIVOTDATA("Amount",Pi vot!$A$12,"Client",$F6,"Period",G$3,"Type",$A6,"Ca tegory",$B6,"Client_Execution",$E6,"Cust",$D6)),0)

If you find a more creative solution let me know, you've peeked my interest

Very Nice.

You could also skip the pivot table all together if you have the data sets and used named ranges/sumproduct to achieve similar results.

This is true. Except that the Pivots are sourced through a data connection to SQL tables. I've been trying to figure out for years how to skip the pivot all together, but you've gotta store some amount of data in the Excel file. It'd be nice if there was an Excel formula that could replicate SELECT, WHERE, AND GROUP BY statements directly to an external data source.

[MoSetsfire]

if you can query the tables directly you can use VBa to bring in your queries and then sumproduct off of them.

[TomAz]

I had coffee with a guy from a major tech firm recently about their jobs for mbas, and asked him what sort of skills were the ones he uses the most from school. Without blinking, he said: "PIVOT TABLES."

I should probably learn to do this.

Zarela Martinez (see, e.g., http://culture.wnyc.org/articles/fea...sine-new-york/ ) likes to tell a story about how when she was just starting in professional cooking she told her friend and mentor Paul Prudhomme that she was intimidated by it, and that for example, she didn't even know the proper way to chop an onion. And Prudhomme told her, the way a professional chef chops an onion is by saying "Bring me a chopped onion!" and one of the underlings does it for him.