HELP!! My Job Depends on it!

[Cozmo35]

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!

 

[Greg Truby]

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. :wink

 

[Tom Urtis]

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.

 

[Greg Truby]

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.

 

[Tom Urtis]

"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.

 

[Cozmo35]

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

 

[Cozmo35]

"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!

 

[Greg Truby]

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?

 

[Cozmo35]

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

 

[Tom Urtis]

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.