topic Re: SAP and Excel sorting in Application Development and Automation Discussions

SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 15:57:17 GMT

SAP and Excel sort the dash (u201C-u201C) character in different ways. Since the dash character shows up in a number of our material numbers, this causes issues when mass-loading lots of changes using MM17. Has anyone been able to change the sort types to match?

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 16:11:32 GMT

I have not notices any chabges in sap and excel sort for the same range of materials numbers with same characters , including special charachters, i.e way it should not affect the upload process since, the materila no is unique, but if you a facing the problem could u plase post the material no where there are sorting errors.

Regards,

Vaibhav B Gawali.

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 16:35:47 GMT

In these examples, Excel will sort the last item between the first two, as if the dash didn't exist. SAP will sort the last item after the first two in the first example and before the first two in the second, as if the dash was a special character which sorts before numbers or letters.

SAP sort:

150P-12

150P-18

150P14-8

N-209-7

N135B-P

N225B-P

Excel sort:

150P-12

150P14-8

150P-18

N135B-P

N-209-7

N225B-P

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 16:45:06 GMT

Small example

If you sort it as text, the output in excel and SAP is the same, otherwise the case you point occurs. Maybe this will help

DATA: BEGIN OF itab OCCURS 0,
line(10),
END OF itab.


itab = '150P14-8'. APPEND itab.
itab = 'N135B-P'. APPEND itab.
itab = '150P-12'. APPEND itab.
itab = '150P-18'. APPEND itab.
itab = 'N225B-P'. APPEND itab.
itab = 'N-209-7'. APPEND itab.


SORT itab as text.
LOOP AT itab.
  WRITE: / itab.
ENDLOOP.

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 18:04:43 GMT

I tried to format the cells as text in Excel 2007 and sort. This did not change my sort order in Excel.

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 21:01:48 GMT

Default sort orders In an ascending sort, Microsoft Office Excel uses the following order. In a descending sort, this sort order is reversed.

Value Comment

Numbers Numbers are sorted from the smallest negative number to the largest positive number.

Dates Dates are sorted from the earliest date to the latest date.

Text Alphanumeric text is sorted left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Text and text that includes numbers stored as text are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

Note If you have changed the default sort order to be case-sensitive through the Sort Options dialog box, then the order for alphabetic characters is the following: a A b B c C d D e E f F g G h H i I j J k K l L m M n N o O p P q Q r R s S t T u U v V w W x X y Y z Z

Logical In logical values, FALSE is placed before TRUE.

Error All error values, such as #NUM! and #REF!, are equal.

Blank cells In both an ascending and descending sort, blank cells are always placed last.

Note A blank cell is an empty cell and is different than a cell with one or more space characters.

Possibly sap uses a different strategy for sorting hence you are getting different results.

Also check this

http://help.sap.com/saphelp_nw04/Helpdata/EN/fc/eb3800358411d1829f0000e829fbfe/content.htm

Regards,

Vaibhav B Gawali.

Re: SAP and Excel sorting

Former Member — Tue, 29 Dec 2009 21:09:57 GMT

You're quite right...the Excel Sort sequence will remain incorrect.... I don't recall the details of MM17, but I'm guessing you're driving the changes directly from the spreadsheet?

If I were in a hurry to do this, I think I would write a little ABAP program to upload into an internal table, sort it the way I wanted, then download back to Excel. Open with Windows Explorer and make it a "real" xlsx or xls sheet, then do the MM17. Lots of steps but one way to ensure you get the SAP-version sequencing.