Customized excel ordering

Question

0.00/5 (No votes)

See more:

Hello,

I have this excel file and I have to do some sort of columns.

In case I need to order columns with numbers in increasing order I used the following code:

C#

Excel.Worksheet sheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
sheet.Sort.SortFields.Clear();
sheet.Sort.SortFields.Add(sheet.Range["E13:E100"], Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);
sheet.Sort.SortFields.Add(sheet.Range["D13:D100"], Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);
sheet.Sort.SetRange(sheet.Range["A13:AP100"]);
sheet.Sort.Apply();
xlWorkBook.Save();

My problem is that I have to put in order a column that has some letters but in particular I do not have to sort it in alphabetical order but with a precise order:
First I would like to have the records with the letters GV, then BU, GNYE and then BK.

Thanks so much

What I have tried:

Until now I do not know how to run the code

Posted 8-Jan-18 3:13am

Federico Barbieri

Updated 8-Jan-18 5:59am

CHill60

v2

Add a Solution

Comments

ZurdoDev 8-Jan-18 9:36am

How would any program know to sort it that way?

Federico Barbieri 8-Jan-18 9:37am

Maybe there is the possibility of inserting an array of strings on how I would like the column to be ordered.

2 solutions

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)

CHill60 · Answer 1 · 2018-01-08T05:59:00

Solution 3

An alternative within Excel would be to have an additional column, hidden if necessary that contains a formula to map those items to a number e.g. (untested)

= IIF(A1="GV",1,IIF(A1="BU", 2, IIF(A1="GNYE",3,4)))

Then sort on that column

Posted 8-Jan-18 5:59am

CHill60

Richard MacCutchan · Answer 2 · 2018-01-08T03:37:00

Solution 2

~~Use the Data -> Sort menu item, and select the sort key column. You can then add a Custom List to tell it the order to sort.~~

Seems that this option is not available in the Interop.

I can only suggest extracting the data to a grid or generic list. You can then sort the items into whatever order you require and write them back to the spreadsheet.

Posted 8-Jan-18 3:37am

Richard MacCutchan

Updated 8-Jan-18 3:48am

v2

Comments

ZurdoDev 8-Jan-18 9:38am

I know you are new here, but you posted twice. ;)

Richard MacCutchan 8-Jan-18 9:49am

Hey. I've only been around for 14 years, so I am still learning. :(

Federico Barbieri 8-Jan-18 9:39am

Sorry if I explained myself badly but I intend to do this sort through the C # code

Richard MacCutchan 8-Jan-18 9:49am

See updated suggestion.