Keep adjacent matrix row data when filtering a separate related matrix for min values in Mathcad

[RE_12882698]

Please see the attached file for more information. This is related to my previous post from 11-Feb-2025. Essentially, I have several single column matrices that when augmented form a table of data. Data for one column in the large table is contained in Matrix A which has repeated values. Thus, Matrix A was filtered (my previous post) such that only unique values from Matrix A were used to populate Matrix C. The minimum values in Matrix B for a repeated Matrix A value were extracted and contained in Matrix D (again covered in my previous post).

Please help to produce another matrix (Matrix "Gkeep") that contains the data in an adjacent column (i.e., another separate related matrix called Matrix G) that has the same row entry value as the row kept after filtering for unique values in A and minimum values in Matrix B. Hence, the original large table would be made by augmenting Matrices A, G & B. The summary table would be Matrices C, Gkeep & D.
I plan to use this for several columns, but a single case I can use repeatedly to create the summary table would be fine.   

I am using Mathcad Prime 10.

[SPaulis]

If "A" is your array that you use to find duplicates in and you want to use that to filter arrays B, C , D, E, F, G, etc., the repeat your program but replace array "B" for whatever other array you want filtered.  It would be even better if you made one generic function so that you do not need to duplicate the program every time.

[RE_12882698]

Thank you.

[Werner_E]

Its not clear to me which value in vector G you would like to keep in case of the following demo data

All values correspond to A=3. In B 10 remains because its the smallest value in B corresponding to A=3.

But which value of vector G should remain?

1. 5 in, because its the value of the first row with A=3
2. 4 in, because this value corresponds to the smallest value in B
3. 3 in, because its the smallest value in G

The solution of @SPaulis  gives you option #3.

From your description I got the impression that you mean option #2

Unfortunately with your test data the result would be the same for all three options.

Furthermore, if #2 is meant, you have to specify which value of G should remain in a case as in your test data with A=4. The minimum value in vector B is 21 but this value is present twice. The corresponding values in G are different (0.8 in and 2.3 in). In you sheet you write that you want to keep the 0.8 in. Why? Because its the smallest of the two values or because its the value in the first row with A=4 and B=21?

BTW, Prime can deal pretty well with units. You don't have to divide by "m" and then multiply 39.37.... Simply divide by the unit you want to see the values displayed in:

Instead of using text boxes outside of the matrix you may add a header row

[Werner_E · Accepted solution]

Here are functions creating the matrices in one go for all four mentioned interpretations of your demand.

#3 could be written much shorter but I used the same basic approach for all four.

Using your test data all four functions return the very same final matrix. This is mainly because the data in your vectors is fairly sorted in ascending order.

So here is some test data to demonstrate the difference of the functions. Numbering #1, #2a, #2b and #3 was done to match the numberin in my last answer here in this thread.

My best guess is that you are looking for #2b, right?

[RE_12882698]

Thank you. This is awesome!!!

The solution I was after was #2a. I have implemented it into my calculation and everything is working perfectly.

The reason that I wanted #2a is that when it is used on many columns (i.e., matrix H, I, J, K, etc.), the values that result are consistent across a row (index number) from the original table of values.

If I had used #2b, it would have the potential to select results for the summary of "H" that are from a different row (case) to those in the summary of "J" if the issue discussed earlier were to occur (i.e., multiple cases of the same Dmin combined with the same unique A). 

Superb solution set. Thanks again. Very much appreciated.

[RE_12882698]

Thank you for your response. 

You have understood my question correctly. Yes, I would like the function to return the value for option 2 where G = 4 in in your example, "because this value corresponds to the smallest value in B". I am basically trying to do with Mathcad matrices what I would do in Excel by hiding (or removing) rows that do not produce the governing result in "B" for a given unique value in "A".   Thus, I intend for the loop to return the value in "G" that has the same row index number as that which contained the minimum in B for unique values in A.

You are correct about the more difficult situation being the case when the minimum value in "B" was repeated for the same "A" value but with a different "G" value. If two rows have the same Unique(A) and Bmin combination, then the options would be to either keep both (this sounds hard to do to me), or what I have in mind is to keep only one to be shown in the summary table (i.e., augmented filtered matrices C, D, G_keep) but alert the user to fact that an alternative row has been filtered out. I have explained this further below.

To handle the situation where the Unique(A) and Bmin combination occurs more than once, then it may be best to write two loops as follows:

The first loop would return the first corresponding value in "G" for a Unique(A) and Bmin combination.  

The second loop would return a matrix that shows:

["Unique(A)"   "Bmin"   "Total number of rows with the same "Unique(A)" and "Bmin combination"].

Where the third column above has a value greater than 1, this would alert the user to the fact that there are additional rows they might want to look at in the unfiltered matrix to ensure they have not filtered out an important case. I think this would be a very useful check. This would enable a third simple loop along the lines of if "max(second loop result) = 1, then "OK", Else "check original table for additional rows that satisfy the governing case".

The way of adding a header to the matrix is clever and I will definitely use it. Grateful for that tip.

Hope this helps clarify my request. If you have a better suggestion for how to handle this case, I'd be happy to hear it.

[Werner_E]

---

You are correct about the more difficult situation being the case when the minimum value in "B" was repeated for the same "A" value but with a different "G" value. If two rows have the same Unique(A) and Bmin combination, then the options would be to either keep both (this sounds hard to do to me)

Its not so hard. Find attached a new function mkMat#2c(...) which keeps all rows with the same Bmin value.

Using the data you provided we also now get two rows with A=4 and B=21

In #2a and #2b I already had created a submatrix tmp2 containing all the rows with the same minimum value of B. in #2a I used it to select the first row it, in #2b I used it to select the minimum of all G (=C in my programs) values.

Now in #2c I simply loop through this matrix tmp2 to select all rows.

Prime 10 sheet attached

[RE_12882698]

Thank you. I will make use of this updated solution.
Again, very much appreciated.