PDA

View Full Version : Excel help?


Colapop
26th March 2006, 10:58
What are ya like with excel? I need a fromula to sort cells to show me the the drawing list in order and the latest revision. At the moment using sort I get this...

E43,D,Floor plan
E43,E,Floor plan
E44,D,Floor plan
E44,E,Floor plan
E45,D,Floor plan
E45,E,Floor plan

I want to have the data appear...

E43,E,Floor plan
E44,E,Floor plan
E45,E,Floor Plan.... (or whatever the latest revision is)

Anyone? Yeah I'm sooo happy to be at work on a Sunday!!
Karma
26th March 2006, 11:18
You're sorting it the wrong way...

You need to sort it by the second column first, then the first one.

If you use the sort function in the menu bar at the top (Data > Sort IRC), then you can sort it by two datasets at the same time.
Colapop
26th March 2006, 11:31
Then I don't get the E44 drawing numbers in order. I have 450 drawings that are a mix of drawing number and revisions and are in different sets (stick files). I deally I want to end up with a list that shows all the drawings in order (1-5) and in revision order.
So the list may end up being 1A, 2A, 3B, 4C, 5A. (there's a 3A, 4A, 4B etc) All other previous revision numbers I don't care about.
I thought something like data sort but with a formula included? Or is that just trying to complicate things?
Bekki
26th March 2006, 12:17
Try this Cola...

If you have three columns of data as in your example above... eg A,B,C..

(1) Do a Select All and sort
(2) Paste the formula =if($a1<>$a2,a1,"") into three empty columns beside each row of info (eg cols D,E,F)
This should show all the info from all the last iterations only.
(3) Select the new data from the three new columns and sort these columns only. (Alteratively copy the new data and "Paste Special -> Values" into a new worksheet and then Sort All).. This will get rid of all the blank lines.

Voila!

(Note that this assumes your info starts at column A.. if it starts at another column you will need to change the formula accordingly.)

If it works you can probably put it into a macro for ease of future use. :)
Colapop
26th March 2006, 12:44
Thanks B - I owe you a beer (or other beverage of your choosing...)! :drinknsin