Re-ordering of data by criteria - 20lbs brain needed!

[MartinCouture]

Ok, pretty simple right ?

data in Excel spreadsheet is: (it doesn't paste nice, but they are all lined up by column in the spreadsheet)

Arrive	Place		Depart	Flt	Crew	Tail#	Rmks

          Trenton		262100	3446	89	003	VAC
262145	Ottawa		262330		89		
270645	Lesquin        031130		89		RON
031930	Ottawa		032100		89		
032145	Trenton					
						
	Trenton		291400	3448	79	004	
291745	Edmonton	291930		79		
292330	Fredericton	300100		79		
300200	Trenton				79	

	Trenton		271600	202	10	003	CA
280100	Zagreb		280500		9		
280815	Tel Aviv	281030		9		
281315	Zagreb		281800		10		D/H
290200	Halifax		290400		10		
290530	Trenton		290730		11		
291000	Winnipeg	291130		11		
291330	Edmonton	291500		11		
291630	Comox		301700		11		RON
301830	Edmonton	302000		11		
302200	Winnipeg	302330		11		
010215	Trenton				11

It's basically a schedule.

I want as a result the whole mess sorted by crew:

9:
280100	Zagreb		280500		9		
280815	Tel Aviv	281030		9		
281315	Zagreb		281800		10		D/H

10:
	Trenton		271600	202	10	003	CA
280100	Zagreb		280500		9		
281315	Zagreb		281800		10		D/H
290200	Halifax		290400		10		
290530	Trenton		290730		11		

11:
290530	Trenton		290730		11		
291000	Winnipeg	291130		11		
291330	Edmonton	291500		11		
291630	Comox		301700		11		RON
301830	Edmonton	302000		11		
302200	Winnipeg	302330		11		
010215	Trenton				11

Any takers???

I'm currently trying with TEXT functions like SEARCH and LOOKUP but no success. I named each column 'crew', 'arrive', 'depart', 'place', etc... just like in the column header.

I'd like to use the sort command, but can't seem to be able to program the $#@ thing.

-----------------
[flush]

 

[SkipVought]

Hi,

I'd use the PivotTable wizard, but you must select ALL your data and the headings since you have empty rows.

I formatted a PivotTable in about 10 seconds. Maybe 2 more minutes to pretty it up.

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]

 

[xlbo]

not sure this has come across well. Rather than describing the situation, what is the actual problem ?

 

[MartinCouture]

Skip:
Thanks, I'm looking up pivot tables right now. The only thing is that it's giving me math results instead of displaying the text like: 'count of place' instead displaying the actual text of the field 'place'. The other options are all math related...but I'll keep looking at it.

xlbo:
The problem is that we receive a list of flights maned by different crews sorted by plane number. Operations does not care if it's sometimes hard to decode or find what crew is doing what, and the list is rather large. A crew can do multiple missions while on the road, so we want the same list but sorted by crew number.

I has to be relatively easy for the schedulers to sort, IE execute a macro, or refresh a different sheet that contains the formulas or even have vba scripts.

It's easy when I'm in the office for the schedulers, I just cut and paste all the flights into a single column and sort using the crew row, but the data cannot have empty lines, so there's a lot of work that could be automated...

-----------------
[flush]

 

[xlbo]

Martin - sorry - I meant what is the excel problem.

Is it that you need to fill in some blanks??
IS it that your columns are in the wrong order ?
or is it that you just need a macro to sort into order?
or is it that you have rows which don't fit nicely into the rest of the dataset ??

 

[theo1881]

as i understand it you want the spreadsheet to be sorted by crew ?

then surely you can just use a data, sort, followed by data, subtotals ?

Laters, phat, headshape

 

[KenWright]

No matter what you do I think you will have to do a bit of data cleansing first, though once that is done I would have thought that Sort would get you pretty much what you want, even though personally I'd probably use a Pivot table because it breaks it up nicely for me.

In terms of data cleansing, I would lose the blank rows between existing sets of data by picking on whichever field (Place by the look of it) is guaranteed to have a value in it in every row where there is data, then doing Edit / Go To / Special / Blanks, then doing Edit / Delete / Entire Row.

Now select all the data in the 'Flight' column, do Edit / Go To / Special / Blanks, then type = and hit the UP arrow once and then hit CTRL+ENTER. That gives you a Flight number against every record (Assuming that would be correct). Copy that column and paste in situ with paste special/ values.

Now just Pivot table and then drag 'Crew' into the Row fields, then drag in 'Place' / 'Depart' / 'Flight' just to the right of each rightmost existing value in the row fields, ie 'Place' just to the right of 'Crew', 'Depart' just to the right of 'Place' etc. Note that you do NOT want to drag the data into the DATA field, though having said that, when all is finished I would probably drag 'Flight' in to the DATA fields as well at the end, just to get rid of the big white box. You'll end up with a series of 1s, but hey.

Use Field settings to get rid of the superfluous Totals that will be all over the page and you should be set.

You would also get a bunch of manky '(blank) listings, but could easily cure that with conditional fomatting and

cell value is equal to ="(blank)" and white font

Don't understand some of the blanks that I see, but I guess it just means that crews haven't been assigned etc.



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MartinCouture]

Thanks a lot guys !

Didn't know about the goto/special to find blank cells. That makes it easy to clean up and copy missing data.

I'll keep learning about the pivot table, I think that will be the answer for us.

Thanks again.

-----------------
[flush]

 

[KenWright]

Nice intro to Pivot Tables here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------