Get Help

Turn on suggestions

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

Showing results for

- Community
- :
- PTC Mathcad
- :
- PTC Mathcad
- :
- SORT TABLE

Options

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

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

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
12:28 PM

01-06-2021
12:28 PM

Hello everyone.

I have extensive tables with data with which I must do repetitive operations, I have managed to make the calculations automatically in mathcad and obtain a table with the results. What I can't do is sort the final table to sum one of the result columns.

I attach the file in Mathcad Prime with the table of results that I obtained and with the ordered table that I need to obtain.

I hope you can help me, in advance thank you very much for the help.

Solved! Go to Solution.

Labels:

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-07-2021
08:05 PM

01-07-2021
08:05 PM

Here is a function which, as I believe, does what you are looking for:

Prime 6 worksheet attached

17 REPLIES 17

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
12:48 PM

01-06-2021
12:48 PM

Re: SORT TABLE

Try using the sort functions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
02:08 PM

01-06-2021
02:08 PM

Re: SORT TABLE

I don't think that Mathcads sort functions will suffice to do the task, some programming will be required.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
01:49 PM

01-06-2021
01:49 PM

Re: SORT TABLE

All I can see are two EXCEL tables. How did you get the second (right) table? Was that order generated in Mathcad? Where are those calculations?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
01:56 PM

01-06-2021
01:56 PM

Re: SORT TABLE

The second table is a picture, looks like a screenshot from Excel.

Luc

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
02:10 PM

01-06-2021
02:10 PM

Re: SORT TABLE

@LucMeekes wrote:

The second table is a picture, looks like a screenshot from Excel.

Luc

No, its an Excel component, too. But without any in/output from/to Mathcad

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
01:54 PM

01-06-2021
01:54 PM

Re: SORT TABLE

Use the csort() function (look it up in the help):

Repetitively using the csort on different columns - may - get what you need.

Success!

Luc

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
02:12 PM

01-06-2021
02:12 PM

Re: SORT TABLE

Note, that the result table uses some rows multiple time.

That result table is kind of connecting different paths which may contain partially equal subpaths

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-06-2021
02:09 PM

01-06-2021
02:09 PM

Re: SORT TABLE

I wonder why you don't do the necessary calcilations and table build-up in Excel with a bit of scripting.

As I understand you provide an Excel table, want Mathcad do the build-up of a new table and write it back to another Excel sheet, right?

Du you really need the table as you had shown (I don't think that Mathcad can create an Excel sheet with combined cells as you use for your sums.

Would it suffice to create a table like

Design. 100; Start 0; End 21; SUM 0.257

Design. 101; Start 2; End A; SUM 0.186

Design. 101; Start 2; End B; SUM 0.431

.....

Or do you need all the hops and values from COL-A and COL-B

COL-C seems unnecessary as its just the difference between COL-A and COL-B, so the values could be calculated rather than taken from a table.

Why does DESIGN. 204 not showing up in your second table?

A further question:

Can we assume that within every DESIGNATION every "path" starts with the same number? For example, both paths for 102 start with 10 and all three paths for 103 start with 6.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-07-2021
12:09 AM

01-07-2021
12:09 AM

Re: SORT TABLE

First of all thank you all for your comments.

Regarding inquiries:

1. the data tables that I have are in excel and are like the ones shown in the table on the left of the attached file, with the difference that it contains more columns that I use to do other operations between them, which I did with mathcad and that is why I intend to do everything directly in that program. However, if ordering the table so that it looks like the table on the right of the attachment is more complicated than I thought using mathcad, I would have to choose to try to do it directly in excel using programming as stated.

2. The sums column does not need to be in a merged cell. It is enough to create a table with the designation, the starting and ending cells, and the sum of the COL-C column, ignoring the COL-A and COL-B columns.

Design. 100; Start 0; End 21; SUM 0.257

Design. 101; Start 2; End A; SUM 0.186

Design. 101; Start 2; End B; SUM 0.431

The colors used and the detail shown in the target table (right) is so that I can better understand what I want to achieve.

3. Designation 204 should be included in the target table (right), it is not included due to an error when copying it to the attached file.

4. Within each designation, each path begins with the same number in all cases. All paths in 102 start with 10 and all paths in 103 start with 6.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-07-2021
01:44 PM

01-07-2021
01:44 PM

Re: SORT TABLE

You shouldn't need programming in Excel to get the sorting on multiple columns that you need. A single sort can involve multiple successive columns.

Success!

Luc

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-07-2021
06:15 PM

01-07-2021
06:15 PM

Re: SORT TABLE

As already written above I fear that more is needed than a simple resorting of the table!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-07-2021
08:05 PM

01-07-2021
08:05 PM

Here is a function which, as I believe, does what you are looking for:

Prime 6 worksheet attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-08-2021
10:21 AM

01-08-2021
10:21 AM

Re: SORT TABLE

It works perfectly, thank you very much Werner.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-08-2021
10:40 AM

01-08-2021
10:40 AM

Re: SORT TABLE

Glad I could help.

As long as the input data is "well behaved", the functions should work, but there is no error checking built-in.

It may be a good idea to sort the IDs by changing the second line in mkNewList to IDs<--sort(delDupes...).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-08-2021
10:52 AM

01-08-2021
10:52 AM

Re: SORT TABLE

I incorporated the "sort()" function to sort the table in ascending order based on the first column as you suggest, again thanks for the help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-08-2021
10:57 AM

01-08-2021
10:57 AM

Re: SORT TABLE

Please also note that during testing the ORIGIN-awareness of my functions I changed ORIGIN to different values and the sheet I posted is now set to ORIGIN=1 instead of ORIGIN=0.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-08-2021
11:12 AM

01-08-2021
11:12 AM

Re: SORT TABLE

Ok, thank you very much.