Sort on excel merged cells on single parameter keeping block intact

Question

0.00/5 (No votes)

See more:

Hello All,

Need a hand in sorting this excel data in a weird way!!

I have data in following manner:

Name  |     Stat     |     Value

Emp1  |     outc     |      2
Emp1  |     inc      |      NA

Emp2  |     outc     |      10
Emp2  |     inc      |      4

Emp3  |     outc     |      6
Emp3  |     inc      |      20

Now I need to sort this data on 'out' value in descending order but at the same time, the emp name block is to be intact i.e other parameter values will not be sorted. Output hence will be

Name  |     Stat     |     Value

Emp2  |     outc     |      10
Emp2  |     inc      |      4

Emp3  |     outc     |      6
Emp3  |     inc      |      20

Emp1  |     outc     |      2
Emp1  |     inc      |      NA

Please help!! I am using excel vba. Any suggestion will be very helpful!

What I have tried:

I have unmerged the emp cells and then sort the data. But the block is not remaining intact.

Posted 19-Jul-17 21:07pm

planetz

Updated 20-Jul-17 1:14am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pat O'Brien · Answer 1 · 2017-07-20T01:14:00

The easiest would be to create a sort helper column:

assuming Name is col C, Stat is col D and Value is col E the formula in row 5 would be:

=TEXT(IF(C5=C4,E4,E5),"000000")&C5

The sort would then be on the column this formula is in (Descending) and the second key column would be the Stat column (Descending).

So the formula... if the Emp# is the same as the row above, use the row above value, otherwise use the value on the same row.