Page 1 of 3 123 LastLast
Results 1 to 30 of 79

Thread: Excel Help

  1. #1
    The Fro PassiveTheory's Avatar
    Join Date
    Jan 2007
    Location
    San Diego, CA
    Posts
    11,040

    Default Excel Help

    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?
    Upcoming Shows:
    * Dillon Francis + Anamanaguchi -- December 28th

    Going to Weekend 1 of Coachella 2014


    Quote Originally Posted by TomAz View Post
    Cara. Don't judge a man for not being able to formulate a coherent thought. On an internet forum of all places.

  2. #2
    Member sydaud's Avatar
    Join Date
    Jan 2007
    Location
    Detroit
    Posts
    1,236

    Default Re: Excel Help

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

  3. #3
    Member anti-square's Avatar
    Join Date
    May 2007
    Location
    She lives in my lap
    Posts
    2,247

    Default Re: Excel Help

    Yup. Beat me to it.

  4. #4
    The Fro PassiveTheory's Avatar
    Join Date
    Jan 2007
    Location
    San Diego, CA
    Posts
    11,040

    Default Re: Excel Help

    Thank you!
    Upcoming Shows:
    * Dillon Francis + Anamanaguchi -- December 28th

    Going to Weekend 1 of Coachella 2014


    Quote Originally Posted by TomAz View Post
    Cara. Don't judge a man for not being able to formulate a coherent thought. On an internet forum of all places.

  5. #5
    Member sydaud's Avatar
    Join Date
    Jan 2007
    Location
    Detroit
    Posts
    1,236

    Default Re: Excel Help

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

  6. #6
    The Fro PassiveTheory's Avatar
    Join Date
    Jan 2007
    Location
    San Diego, CA
    Posts
    11,040

    Default Re: Excel Help

    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...
    Upcoming Shows:
    * Dillon Francis + Anamanaguchi -- December 28th

    Going to Weekend 1 of Coachella 2014


    Quote Originally Posted by TomAz View Post
    Cara. Don't judge a man for not being able to formulate a coherent thought. On an internet forum of all places.

  7. #7
    Act Like You Know real talk's Avatar
    Join Date
    Oct 2008
    Location
    Redmond, WA
    Posts
    2,763

    Default Re: Excel Help

    Don't you have a god damned textbook?
    Quote Originally Posted by TomAz View Post
    You were right. I was wrong.

  8. #8
    LOLocaust Survivor Hannahrain's Avatar
    Join Date
    Feb 2007
    Location
    stately Rain Manor
    Posts
    17,457

    Default Re: Excel Help

    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.

  9. #9
    Bambi menikmati's Avatar
    Join Date
    Nov 2006
    Location
    North Hollywood, CA
    Posts
    15,062

    Default Re: Excel Help

    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.

  10. #10
    The Fro PassiveTheory's Avatar
    Join Date
    Jan 2007
    Location
    San Diego, CA
    Posts
    11,040

    Default Re: Excel Help

    Thanks, Erik
    Upcoming Shows:
    * Dillon Francis + Anamanaguchi -- December 28th

    Going to Weekend 1 of Coachella 2014


    Quote Originally Posted by TomAz View Post
    Cara. Don't judge a man for not being able to formulate a coherent thought. On an internet forum of all places.

  11. #11
    Coachella Junkie PlayaDelWes's Avatar
    Join Date
    Mar 2007
    Location
    The Sprawl
    Posts
    7,345

    Default Re: Excel Help

    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???



  12. #12
    Coachella Junkie fatbastard's Avatar
    Join Date
    Nov 2006
    Location
    Pasadena
    Posts
    12,272

    Default Re: Excel Help

    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.
    Whiskey Sour

    2 oz blended whiskey
    Juice of 1/2 lemon
    1/2 tsp powdered sugar
    1 cherry
    1/2 slice lemon

    Shake blended whiskey, juice of lemon, and powdered sugar with ice and strain into a whiskey sour glass. Decorate with the half-slice of lemon, top with the cherry, and serve.

  13. #13
    Member MoSetsfire's Avatar
    Join Date
    Nov 2011
    Location
    Dallas, TX
    Posts
    567

    Default Re: Excel Help

    Quote Originally Posted by PlayaDelWes View Post
    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?
    Quote Originally Posted by nerdtram View Post
    if you can't pull off getting drugs into a festival and consuming them, you don't deserve them.
    Quote Originally Posted by ods.. View Post
    Wait. You like Cudi but you think Outkast is a one hit wonder.

    Somebody shoot this fuck.

  14. #14
    Coachella Junkie PlayaDelWes's Avatar
    Join Date
    Mar 2007
    Location
    The Sprawl
    Posts
    7,345

    Default Re: Excel Help

    Quote Originally Posted by fatbastard View Post
    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))


    Quote Originally Posted by MoSetsfire View Post
    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).

  15. #15
    MENACING Courtney's Avatar
    Join Date
    Jan 2007
    Posts
    15,961

    Default Re: Excel Help

    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.

  16. #16
    old school ThatGirl's Avatar
    Join Date
    Feb 2011
    Location
    Canada
    Posts
    4,141

    Default Re: Excel Help

    Quote Originally Posted by Courtney View Post
    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.
    Quote Originally Posted by M Sparks View Post
    It's all riding on this. You've got big dreams to ride to the top of the Flash Mob world. Well internet fame costs. And right now is when you start paying for it...in sweat.
    Quote Originally Posted by TomAz View Post
    hey. get your own colonoscopy thread, bitch.

  17. #17
    Member MoSetsfire's Avatar
    Join Date
    Nov 2011
    Location
    Dallas, TX
    Posts
    567

    Default Re: Excel Help

    Quote Originally Posted by PlayaDelWes View Post
    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
    Quote Originally Posted by nerdtram View Post
    if you can't pull off getting drugs into a festival and consuming them, you don't deserve them.
    Quote Originally Posted by ods.. View Post
    Wait. You like Cudi but you think Outkast is a one hit wonder.

    Somebody shoot this fuck.

  18. #18
    Member MoSetsfire's Avatar
    Join Date
    Nov 2011
    Location
    Dallas, TX
    Posts
    567

    Default Re: Excel Help

    You could also skip the pivot table all together if you have the data sets and used named ranges/sumproduct to achieve similar results.
    Quote Originally Posted by nerdtram View Post
    if you can't pull off getting drugs into a festival and consuming them, you don't deserve them.
    Quote Originally Posted by ods.. View Post
    Wait. You like Cudi but you think Outkast is a one hit wonder.

    Somebody shoot this fuck.

  19. #19
    Coachella Junkie PlayaDelWes's Avatar
    Join Date
    Mar 2007
    Location
    The Sprawl
    Posts
    7,345

    Default Re: Excel Help

    Quote Originally Posted by Courtney View Post
    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.

    Quote Originally Posted by MoSetsfire View Post
    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.

    Quote Originally Posted by MoSetsfire View Post
    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.

  20. #20
    Member MoSetsfire's Avatar
    Join Date
    Nov 2011
    Location
    Dallas, TX
    Posts
    567

    Default Re: Excel Help

    if you can query the tables directly you can use VBa to bring in your queries and then sumproduct off of them.
    Quote Originally Posted by nerdtram View Post
    if you can't pull off getting drugs into a festival and consuming them, you don't deserve them.
    Quote Originally Posted by ods.. View Post
    Wait. You like Cudi but you think Outkast is a one hit wonder.

    Somebody shoot this fuck.

  21. #21
    Peaceful Oasis TomAz's Avatar
    Join Date
    Nov 2006
    Location
    Zenith, Winnemac
    Posts
    40,846

    Default Re: Excel Help

    Quote Originally Posted by Courtney View Post
    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.
    Quote Originally Posted by efrain44 View Post
    Anyone know who the guy in the Cardinals jersey is? I've seen him in pictures on the board and I thought I saw him this year.

  22. #22
    Stage Manager captncrzy's Avatar
    Join Date
    Jan 2007
    Location
    TBD
    Posts
    19,849

    Default Re: Excel Help

    Ok, someone help me.

    I have a spreadsheet with about 50,000 rows with several matches, so manually adding these will be a nightmare without a formula.

    I need a formula that will add values that are issued by employee number. For example:


    Employee number Last name First name # of awards received
    159696 Gahan Dave 3
    148633 Gore Martin 6
    159696 Gahan Dave 12


    Basically, I need a formula that will look at column A and when two values in column A match, add the totals in Column D, and place that total in column E.
    Odi profanum vulgus et arceo. I hate the unholy rabble and keep them away - Horace.
    Quote Originally Posted by Drinkey McDrinkerstein View Post
    Arcade FIre are a bunch of dicks, Deadmau5 is a dick, bands are dicks, David Bowie sucks dicks, Daft Punk is two human buttholes with semen for brains (that was loaded into a butthole from a dick that grew out of their moms), we're all dicks that fucked our moms assholes, God is going to put a giant dick down and fuck our mouths

  23. #23
    Banned thelastgreatman's Avatar
    Join Date
    Feb 2007
    Location
    The Wasteland (LA)
    Posts
    12,793

    Default Re: Excel Help

    Do you mean looking between two separate sheets? Vlookup might be the way to go.
    Quote Originally Posted by schoolofruckus View Post
    Look, your parenting is yours and Randy's business alone.
    Fans of TheLastGreatMan Accessory Shop

  24. #24
    Banned thelastgreatman's Avatar
    Join Date
    Feb 2007
    Location
    The Wasteland (LA)
    Posts
    12,793

    Default Re: Excel Help

    Now that I think about it though, I'm not actually sure that you could use Vlookup to do a calculation like that. Hmm. I dunno.
    Quote Originally Posted by schoolofruckus View Post
    Look, your parenting is yours and Randy's business alone.
    Fans of TheLastGreatMan Accessory Shop

  25. #25
    Stage Manager captncrzy's Avatar
    Join Date
    Jan 2007
    Location
    TBD
    Posts
    19,849

    Default Re: Excel Help

    No it's all on one giant worksheet. I need it to come back to me saying "employee # 159696 has 15 awards".
    Odi profanum vulgus et arceo. I hate the unholy rabble and keep them away - Horace.
    Quote Originally Posted by Drinkey McDrinkerstein View Post
    Arcade FIre are a bunch of dicks, Deadmau5 is a dick, bands are dicks, David Bowie sucks dicks, Daft Punk is two human buttholes with semen for brains (that was loaded into a butthole from a dick that grew out of their moms), we're all dicks that fucked our moms assholes, God is going to put a giant dick down and fuck our mouths

  26. #26
    Member insbordnat's Avatar
    Join Date
    Jan 2007
    Location
    In a blazing inferno
    Posts
    1,792

    Default Re: Excel Help

    Jen - do you just need a summary table that will show total awards for each employee based on the data, like:

    Employee number Last name First name # of awards received
    159696 Gahan Dave 15
    148633 Gore Martin 6
    northside groove...southside groove....eastside groove...westside groove

  27. #27
    Stage Manager captncrzy's Avatar
    Join Date
    Jan 2007
    Location
    TBD
    Posts
    19,849

    Default Re: Excel Help

    Yes, that's it.
    Odi profanum vulgus et arceo. I hate the unholy rabble and keep them away - Horace.
    Quote Originally Posted by Drinkey McDrinkerstein View Post
    Arcade FIre are a bunch of dicks, Deadmau5 is a dick, bands are dicks, David Bowie sucks dicks, Daft Punk is two human buttholes with semen for brains (that was loaded into a butthole from a dick that grew out of their moms), we're all dicks that fucked our moms assholes, God is going to put a giant dick down and fuck our mouths

  28. #28
    Member insbordnat's Avatar
    Join Date
    Jan 2007
    Location
    In a blazing inferno
    Posts
    1,792

    Default Re: Excel Help

    Ok, just saw your response. Do you know how to use pivot tables? That'd work for you.
    northside groove...southside groove....eastside groove...westside groove

  29. #29
    Stage Manager captncrzy's Avatar
    Join Date
    Jan 2007
    Location
    TBD
    Posts
    19,849

    Default Re: Excel Help

    No, I'm a pretty basic excel user and look up how to do stuff as needed. If you can list out instructions, that'll help.
    Odi profanum vulgus et arceo. I hate the unholy rabble and keep them away - Horace.
    Quote Originally Posted by Drinkey McDrinkerstein View Post
    Arcade FIre are a bunch of dicks, Deadmau5 is a dick, bands are dicks, David Bowie sucks dicks, Daft Punk is two human buttholes with semen for brains (that was loaded into a butthole from a dick that grew out of their moms), we're all dicks that fucked our moms assholes, God is going to put a giant dick down and fuck our mouths

  30. #30
    Coachella Junkie PlayaDelWes's Avatar
    Join Date
    Mar 2007
    Location
    The Sprawl
    Posts
    7,345

    Default Re: Excel Help

    Done

    =SUMIF($A$2:$A$4,A2,$D$2:$D$4)
    Quote Originally Posted by dj12inches View Post
    What makes me qualified? I've watched EVERY fucking episode of American Idol, and every single episode of The Voice...Forget that I won departmental music awards when I was in the 8th grade choir.

Similar Threads

  1. excel schedule
    By rks1129 in forum Misc. Babble
    Replies: 5
    Last Post: 04-23-2008, 08:41 AM
  2. Another Excel Spreadsheet
    By sddoctor in forum Line Up/Artists
    Replies: 2
    Last Post: 04-24-2007, 12:30 PM
  3. excel set times sheets?
    By psychic friend in forum Music Lounge
    Replies: 72
    Last Post: 02-28-2007, 07:17 PM

Posting Permissions

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