Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Alphabetise or sort cells prior to merge / concatenate

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 12, 2012 9:11 AM
(3932 views)

Hi there,

Having searched and searched through the JMP manual I cannot seem to find a solution to my issue which I am sure is straight forward.

I have several columns which all contain data which is similar based on specific codes. For example:

Code 1 Code 2

K26.3 U20.2

K26.3

K26.2

K26.2 U20.2

U20.2 K26.2

U20.2 K26.2

K26.2

U20.2 K26.3

K26.2 U20.2

U20.2 K26.2

U20.2 K26.2

U20.2 K26.2

K26.2 K33.9

U20.2 K26.2

The end result I am after will sort these codes alphabetically, and then concatenate to create a new code which can then be used for statistical analysis. Think of it as a sorted or alphabetised group.

e.g. the first code could result in "K26.3-U20.2", the second would just be "K26.3" which the last one would be "K26.2-U20.2".

Any ideas how I can do this in JMP?

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Yes it makes it easier, but both above formulas should work.

Without the hyphen this column formula works:

sortlist**(**evallist**({**:Code 1,:Code 2**}))[****1****]**||sortlist**(**evallist**({**:Code 1,:Code 2**}))[****2****]**

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Alphabetise or sort cells prior to merge / concatenate

If I understand the problem right it's not very straight forward as the order matters as well as empty cells must not have the hyphen "-". So a simple concatenate formula without conditional statement wont do.

I think there are several solutions (as often in JMP) to this. Here are two different approaches using a column formula. The first formula is based on conditinal simple conditionals, the second creates a sorted list and use regular expressions to delete any redundant "-".

To try, run this script or just paste the expression inside Fromula() into a the formula property of a new column.

current data table**()**<<new column**(**"Code 1-Code 2",formula**(**If**(** :Code 1 > :Code 2,

:Code 2 || If**(** Is Missing**(** :Code 2 **)**, "", "-" **)** || :Code 1,

:Code 1 || If**(** Is Missing**(** :Code 1 **)**, "", "-" **)** || :Code 2

**)));**

current data table**()**<<new column**(**"Code 1-Code 2",formula**(**

sl = sortlist**(**evallist**({**:Code 1,:Code 2**}))**;

regex**(**sl**[****1****]**||"-"||sl**[****2****]**,"^-*(.+)-*$","\1"**)**

**))**

Message was edited by: Marcus Sundbom

Added second formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Alphabetise or sort cells prior to merge / concatenate

If it makes it easier, the hyphen can be excluded - that is only there to make it easier to split the codes up afterwards.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Yes it makes it easier, but both above formulas should work.

Without the hyphen this column formula works:

sortlist**(**evallist**({**:Code 1,:Code 2**}))[****1****]**||sortlist**(**evallist**({**:Code 1,:Code 2**}))[****2****]**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Alphabetise or sort cells prior to merge / concatenate

That is perfect. I extrapolated the formula out to include additional codes and it works a charm! Many thanks!

sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[1]

||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[2]

||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[3]

||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[4]

||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[5]