filter the names.

[jayanthimurali]

I have got different names in a row i want to filter the names according to the names.
for example if i have got ab,bc, db ef fg gh the names ab, ef gh should be in the move to the first, than bc fg next

customer name	account number
abcd	27587689
lkgh	469790
bcda	4860870
jaya	375879

<colgroup><col><col></colgroup><tbody>
</tbody>

when filtered the names should be

customer name	account number
abcd	27587689
bcda	4860870
lkgh	469790
jaya	375879

<colgroup><col><col></colgroup><tbody>
</tbody>

thanks in advance

 

[jayanthimurali]

thank you so much I was able to do the auto filter one more thing and in the same chart in one of the column i have a date formatted this way 20140812, 20140715 and 20140614 and so many when i do the auto filter and save the result in sheet two, i should also get the result all the dates pertaining to last month should change to green, and to the previous month that is july it should be in yellow and before that month that is june and before that it should change to red.
i do the report every month and the above months were only example so the dates pertaining to previous month should be green before that should be in yellow and before that should be in red.

 

[jayanthimurali]

in other words iam trying to use the conditional formatting if the date is of last month change color to green, or if that is before previous month than change to red

 

[jayanthimurali]

or how to use this formula in conditional formating==DATEVALUE(DAY(TODAY())&"/"&MONTH(A1)+1&"/"&YEAR(A1))=(TODAY())

 

[maheshmaxi]

Hi Jayanthi,

Go to Conditional Formatting --> Click 'New Rule' --> Select 'Use a formula to determine which cells to format' in the list --> Paste 'Green' formula given below. Do the formatting for Green.
And do the same for 'Yellow' and 'Red'

Green : =(VALUE(MONTH(TODAY()))-VALUE(MID(E11,5,2)))=1
Yellow : =(VALUE(MONTH(TODAY()))-VALUE(MID(E11,5,2)))=2
Red : =(VALUE(MONTH(TODAY()))-VALUE(MID(E11,5,2)))>2

-Mahesh

 

[jayanthimurali]

thank you for the reply, actually i have a data which have of many dates which i have given above is an example.
if the date is last month the full row should automatically change to green and if it is before that than yellow and and before that red
and also filter according to the colour first should be green, than yellow and last should be

 

[maheshmaxi]

Use below formulas in conditional formatting for all rows and columns upto your data range (by copy paste format). It will make the whole row with specified color based on date column. I have assumed date is in Column A and created below formulas. If you have date in some other column in your data, just change '$A' in my formulas before copy paste.

Green : =(VALUE(MONTH(TODAY()))-VALUE(MID($A11,5,2)))=1
Yellow : =(VALUE(MONTH(TODAY()))-VALUE(MID($A11,5,2)))=2
Red : =(VALUE(MONTH(TODAY()))-VALUE(MID($A11,5,2)))>2

 

[jayanthimurali]

how to input in conditional formatting
Iam highlighting the "A"
what iam doing is Conditional Formatting --> Click 'New Rule' --> Select 'Use a formula to determine which cells to format' in the list --> Paste 'Green' formula given below. Do the formatting for Green.
once i Paste =(VALUE(MONTH(TODAY()))-VALUE(MID($A11,5,2)))=1 and close the conditional formatting icon
iam not getting the result and when iam opening again iam unable to see the formula
and iwant the fullrow to become green

<tbody>
</tbody>

 

[maheshmaxi]

Which version of Excel are you using?

 

[jayanthimurali]

Green : =(VALUE(MONTH(TODAY()))-VALUE(MID($A:$A,5,2)))=1
Yellow : =(VALUE(MONTH(TODAY()))-VALUE(MID($A:$A,5,2)))=2
Red : =(VALUE(MONTH(TODAY()))-VALUE(MID($A:$A,5,2)))>2

iam getting the full "A" green

A	B	C	D
20140811	fjhhl	cdderkhk	fjhkjl
20140710	fgkhl	fjgjl	jhkjl
20140612	khlj;k;	gjkhjl;lk	jgyut
20150711	mbhlj	gkhlj	gkhlj
20140814	lhj;k	mjhlj	mkhlj
20140418	rgkhl	kjlk	kjk
20140418	,jlgkjlk	kjlk	jtykuj

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[jayanthimurali]

i was able to input the formula but with the above formula the full row "A" is changing into green it is not changing into the other colours
and if as per the condition if the "A1" is to be changed to Green than i want the full column to be changed to green