• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto sort data in MS Excel 2010

A

arslan_janjua2001

New Member
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
 

Attachments

  • PER SHEET.xlsx
    11 KB · Views: 3
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 said:
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))
Click to expand...

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?
 

Attachments

  • Copy of PER SHEET.xlsx
    13.2 KB · Views: 4
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.
 
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)
 

Attachments

  • Copy of Copy of PER SHEET.xlsx
    14.4 KB · Views: 4
You must log in or register to reply here.
Back
Top