sort seniority based on date

Microsoft Book: office excel 2003 inside...
November 18, 2009 at 02:40:28
Specs: Windows XP
How to sort the following dummy data based on date of appointment to find seniority?

Name	Address		       Date of 	      Qualification.
			     Appointment 

las	village tao(las)	12/1/2005	 BE
	p/pare		
	AP		
lap	village tass	        11/6/2005	  MA
	yupia 		
	AP		
lat	taigdo(lat)		10/7/2005	 12 pass
	Ap		
	cist		
	Polo
layi	vill. Tak(lal)		8/8/2005	  PHED
	solo 		
	AP		
	Chyes		
lao	Manu village(i)		1/5/2005  	 Diploma
	Sas		
	AP		
	Perut		
Thom	Satia(Pass)		10/4/2005  	 12th pass
	ano city		
	AP		



See More: sort seniority based on date

Report •


#1
November 18, 2009 at 05:09:37
Hi,

If this sorting by date is not done very often then I suggest using Excel's Sort function manually.

For sort to work, all the data for each individual must be on one row. I know that the database supplied is a dummy database, so I don't know how your real data is arranged. Anyway, get all the data on one row for each individual and the header text on one row, then:
1. Select all the cells containing data plus the header row
2. From the Menu bar select Data - Sort
3. In the dialog box, select My data range has: Header row
4. In the Sort by: section select Date of Appointment from the drop-down list
5. Select Ascending (this will get earliest dates first)
6. Click OK

You will need to be sure that the dates have been recognized by Excel as dates, and are not just text.
Select the column of dates, select Format cells - Number - Date and change the date format. If all the cells change to the selected date format, all is OK. If they are unchanged, then they are likely Text and not dates and will not sort properly. If this is the case let us know and someone can advise on converting the text to dates that Excel recognizes.

If it is not possible to organize all the data for each individual on one row please post back, as a different approach will be required.

Regards


Report •

#2
November 18, 2009 at 20:37:00
Well Humar, I tried that but rows of data of adress column not in the date rows are brought down to the bottom.

I am having problem with Address column as it spread to more than one row and it will be time consuming to retype the adress into single row and my data contain 1247 rows.

Say, this is my existing data:

Name	Address		       Date of 	      Qualification.
			     Appointment 

las	village tao(las)	12/1/2007	 BE
	p/pare		
	AP		
lap	village tass	        11/6/2006	  MA
	yupia 		
	AP		
lat	taigdo(lat)		10/7/2005	 12 pass
	Ap		
	cist		
	Polo

I want to sort them like this:

Name	Address		       Date of 	      Qualification.
			     Appointment 
lat	taigdo(lat)		10/7/2005	 12 pass
	Ap		
	cist		
	Polo
lap	village tass	        11/6/2006	  MA
	yupia 		
	AP
las	village tao(las)	12/1/2007	 BE
	p/pare		
	AP	


Report •

#3
November 19, 2009 at 05:55:50
Hi,

As I pointed out the sort function works only if each entry is on its own row.

Rather than retyping the entries you can bring the data onto single rows with three simple formulas.

Here is some sample data. Note that some entries take up three rows and some four rows.

	A	B			C		D
2	las	village tao(las)	12/01/07	BE
3		p/pare		
4		AP		
5	lat	taigdo(lat)		10/07/05	12 pass
6		Ap		
7		cist		
8		Polo		
9	lap	village tass		11/06/06	MA
10		yupia		
11		AP		

Make a backup copy of your workbook before starting this.

First insert three extra columns before the date column. The dates will now be in column F

Enter the following formulas in the empty cells for the first entry. If your data does not start on Row 2, adjust the formulas accordingly.

C2	=IF(B3="","",B3)
D2	=IF($B4="","",$B4)
E2	=IF(A5="",IF($B5="","",$B5),"")

The reason for the different formula in E2 is to take account of entries that take up either 3 or 4 rows.

Drag the formulas in C2 to E2 down to the bottom of all your entries.
There will be lots of extra data showing, but that will be dealt with during sorting.

Now select all your data.
Copy all data, then Paste special - Values. This will remove the formulas as they are not needed anymore.
Next, sort the data using the date column.
Below the last proper entry will be all the extra data, and this can be selected and deleted.

The sorted data will look like this:

	A	B			C	D	E	F		G
2	lat	taigdo(lat)		Ap	cist	Polo	10/07/05	12 pass
3	lap	village tass		yupia	AP		11/06/06	MA
4	las	village tao(las)	p/pare	AP		12/01/07	BE

Regards


Report •

Related Solutions

#4
November 19, 2009 at 19:55:00
Humar, Thanks for your reply.

Report •


Ask Question