Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: HELP!! My Job Depends on it!

  1. #1
    Board Regular
    Join Date
    May 2009
    Location
    Where the grass is green & the sky is blue!
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy HELP!! My Job Depends on it!

    I have a HUGE spreadsheet that lists every transaction ever done by my company since 2000 (31000+ line items). I have been asked to find out the minimum and maximum price for each part # repaired. Many of them have been repaired more than once and many have a dollar amount of $0.00 which makes it more complex as I need to eliminate that amount. I have used Excel 2003 but I now have 2007. I am far from a guru but I can manage (so far). PLEASE HELP!!!! MY JOB DEPENDS ON IT!

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,998
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Welcome to the board. We'll see if we can keep you from joining the millions in the unemployment line...

    Just pop a pivot table and drop the repair cost into three places: Report Filter and twice as a Value Field. Then just click on the filter field and uncheck the checkbox by the zero value. [Obviously the part # goes in as a row label.]

    Then right click each instance as a value field and pick Summarize By and select Max for one and Min for the other and presto chango, you're still employed (of course now they'll think you're an Excel stud, so the bar will be a lot higher. )
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Gee, based on the urgent tone of this your first post, your Location should read
    Where the grass is green & the sky is falling!

    Seems a pivottable can do this, or at the very least, create a simple table:

    Use Advanced Filter to copy the unique part numbers from whatever column they are in, into the first column of this working table.

    The second column and third column of this working table can house your MIN and MAX formulas respectively.

    So now let's say your working table occupies X1:Z100
    This would be because there are 100 unique part numbers.

    Lets' say your huge list of part numbers occupies A1:A31000 and their prices are listed in B1:B31000.

    In cell Y1 with Ctrl+Shift+Enter copy down to cell Y100
    =MIN(IF($A$1:$A$31000=X1,IF($B$1:$B$31000>0,$B$1:$B$31000)))

    In cell Z1 with Ctrl+Shift+Enter copy down to cell Z100
    =MAX(IF($A$1:$A$31000=X1,IF($B$1:$B$31000>0,$B$1:$B$31000)))

    Note, with those formulas, that's Ctrl+Shift+Enter, not just Enter.

  4. #4
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,998
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Hiya, Tom! How's things out ta SF?

    Obviously I am waaaay lazier'n you. I can pop the pivot thing and be done in less than two minutes if I take my time. Probably less than one if I hustle. [Of course all of this assumes the data is structured in a manner that facilitates use of a pivot.]

    Ya know, fer a feller that seems so panic-stricken, I woulda thought he'd've responded already.
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    "Ya know, fer a feller that seems so panic-stricken, I woulda thought he'd've responded already."

    Ha, you left yourself or me open on that one...maybe he's testing our self-proclaimed "fast" methods. I always wonder with my posts whether my instructions are too much for people who know the basics already, or not enough if they are beginners and I left out important info. Or maybe the boss really did a Donald Trump impersonation.

    I bet Cozmo keeps his job, anyone this dedicated on a Friday afternoon would be a valuable asset to a company.
    Last edited by Tom Urtis; May 29th, 2009 at 04:14 PM.

  6. #6
    Board Regular
    Join Date
    May 2009
    Location
    Where the grass is green & the sky is blue!
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    You guys are the GREATEST!! Thanks for your help!

  7. #7
    Board Regular
    Join Date
    May 2009
    Location
    Where the grass is green & the sky is blue!
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Quote Originally Posted by Tom Urtis View Post
    "Ya know, fer a feller that seems so panic-stricken, I woulda thought he'd've responded already."

    Ha, you left yourself or me open on that one...maybe he's testing our self-proclaimed "fast" methods. I always wonder with my posts whether my instructions are too much for people who know the basics already, or not enough if they are beginners and I left out important info. Or maybe the boss really did a Donald Trump impersonation.

    I bet Cozmo keeps his job, anyone this dedicated on a Friday afternoon would be a valuable asset to a company.
    LOL! Thanks Tom,.... I wouldn't catogorize me as "deticated", it just pays better than un-employment does!

  8. #8
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,998
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Quote Originally Posted by Cozmo35 View Post
    You guys are the GREATEST!! Thanks for your help!
    Yeah, but you left how who's method you used. Unless you tell us that, how are Tom and I supposed to know which of us buys the first round the next time we see each other?
    Last edited by Greg Truby; May 29th, 2009 at 04:57 PM.
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  9. #9
    Board Regular
    Join Date
    May 2009
    Location
    Where the grass is green & the sky is blue!
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Greg, your reply seemed simple so I tried it and it worked. Problem solved. Thanks to both of you!!

  10. #10
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: HELP!! My Job Depends on it!

    Well, looks like I somehow got roped by the Kansas City Excelerator into ponying up the first round. You got it G-Man, at the next Summit if not before.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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