Auto sort data in MS Excel 2010

[arslan_janjua2001]

I have employees data on sheet 1. I want that sheet 2 must sort this data automatically first department wise and then rank wise with G1 senior most and G3 junior most.
need help on this please

 

[AliGW]

Duplicate: https://chandoo.org/forum/threads/auto-sort-data-in-excel-2010.44811/

 

[arslan_janjua2001]

Duplicate: https://chandoo.org/forum/threads/auto-sort-data-in-excel-2010.44811/

need help on this...

 

[pecoflyer]

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

 

[GraH - Guido]

arslan_janjua2001,
Which Excel version do you have?

EDIT: sorry, it is clearly mentioned in your title. This summer heat is killing me .

 

[Luke M]

Duplicate: https://chandoo.org/forum/threads/auto-sort-data-in-excel-2010.44811/

Other thread has now been closed; please continue in this one.

 

[GraH - Guido]

arslan_janjua2001

Maybe use a helper column in Sheet 1:
[G3]=COUNTIF($D$3:$D$13,"<"&D3)+COUNTIF($D$3:D3,D3)

Sheet 2:
[A3]=INDEX('SHEET 1'!$A$3:$E$13,MATCH(ROW(A1),'SHEET 1'!$G$3:$G$13,0),COLUMN(A1))

 

[arslan_janjua2001]

arslan_janjua2001

Maybe use a helper column in Sheet 1:
[G3]=COUNTIF($D$3:$D$13,"<"&D3)+COUNTIF($D$3:D3,D3)

Sheet 2:
[A3]=INDEX('SHEET 1'!$A$3:$E$13,MATCH(ROW(A1),'SHEET 1'!$G$3:$G$13,0),COLUMN(A1))

it really helped. thanks a lot

 

[arslan_janjua2001]

arslan_janjua2001

Maybe use a helper column in Sheet 1:
[G3]=COUNTIF($D$3:$D$13,"<"&D3)+COUNTIF($D$3:D3,D3)

Sheet 2:
[A3]=INDEX('SHEET 1'!$A$3:$E$13,MATCH(ROW(A1),'SHEET 1'!$G$3:$G$13,0),COLUMN(A1))

i have done it and achieved that list sorted department wise. It is possible to cover the second element of arranging them according to rank i.e. G1 being the senior most and G3 being the junior most?

 

[GraH - Guido]

Hi,
can you tell me if your departments are indeed like LAB 1, LAB 2 etc.
Because LAB 10 is ordered before LAB 2, etc. Where you may expect an order from LAB1 , LAB 2, LAB 10.

Also there are only 3 levels G1, G2, G3?

Thanks for clarifying.

 

[GraH - Guido]

Not 100% sure it is watertight. But see attached and test.

Helpers (A-Z):
[H1]=COUNTIF($D$3:$D$13,"<"&D3)*100+COUNTIF($A$3:$A$13,"<"&A3)*10+COUNTIF($A$3:$A3,A3)
[G1]=RANK.EQ(H3,$H$3:$H$13,1)

Helpers (A-Z; 0-9):
[K3] (to be confirmed with CSE) = MID(D3,1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D3&"0123456789"))-1) & TEXT(MID(D3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D3&"0123456789")),10),"000")*1000 +TEXT(MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),10),"00")*100
[J1]= COUNTIF(K$3:$K13,"<"&K3)+COUNTIF(K$3:$K3,K3)