Matching values in a row of a table

[IRarch]

Hi,

I have an extensive table with 5 columns (0 to 4) and more than 1000 rows!

I want to select the values from dorp down lists for the first 4 variables (each variable in one of the first 4 columns). Then I want to find the row that contains all the exact values I selected previously so to find the value of the 5th column (4) at that row (the row with the exact values). No row in the entire table repeats.

Attached is the table in an excel file I will be using.

I would like to be able to select the Height, Width, HFX Condition, Bolt Diameter and then get the corresponding Seismic Allowable Shear value.

Any ideas? Thanks!

[Werner_E]

What you are looking for should be able to do with a routine from Richards fine collection of scriptable components (which I can't find now in this new, horribly forum) using Mathcad 15, bur not with Prime.

Your list includes a lot of duplicates concerbning the first four columns and the bolt diameter it "1.125 STD" throughout.

So which result do you expects with Height=78, Width=9, HFX Cond = Concrete 2500psi and bolt d. =1.125 ?

[IRarch]

Hi @Werner_E,

You are right, I forgot to add one more column. Plese see revised file. The table is much more complex but I was trying to simplify it. The idea is that no row repeats in a table of 1000+ rows.

I'll appreciate if you do find the routine or if you can send me on the right direction.

Thanks!

[RichardJ]

Does this not do what you want?

[IRarch]

Hello @RichardJ

I am still woriking on it with your example HFASU.

I think this will do it. I will show you as soon as I get done with it. 

Thank you so much for all of this.

[IRarch]

Hi @RichardJ

What I have so far is that I am getting a matrix with the rows where the HardyFrame.Width, HFX.InstallationCondition, and Bolt.Diameter.Grade match but I am also getting a 0 in the matrix where on row 0 is not supposed to match all three parameters. There seems to be something wrong where I am gettting 0's.

PS. I changed the StripDuplicateRows program where I made the submatrix (=) instead of (not equal).

Im trying to attach the mathcad file but I get the following error message

The contents of the attachment doesn't match its file type

[Werner_E]

This horrible new forum doesn't have its act together.

Some of us are able to directly upload Mathcad files, but most of us get the very same error message you experience.

Workaround is to put the file in a zip archive and upload the zip file 😞

[IRarch]

File compressed

Thanks @Werner_E

[Werner_E]

Why did you changed Richards "not equal" to "equal".

What do you think this new routine is doing?

If your intention was to filter for the values which are present in two vectors, thats sure not what this routine can do.

See in Richards sheet one way how it could be done.

[IRarch]

I changed it because we want to see the duplicate rows. Not sure why I still get the 0's though.

[Werner_E]

Duplicate rows?

Do you mean values which are present in both vectors? How should the routine find that out if you provide just one stacked vector as argument?

Or what exactly should the routine do with the vector argument? Only showing the numbers which occur more than once?

[Werner_E]

If my first guess wa right, you may be looking for something like this

I don't investigate why your modified routine is not doing this as the routine was designed by Richard to do soemthing completely different one.

BTW you can use "intersect" also for sorting a vector and removing duplicate values if you provide the same vector twice as argument.

[Werner_E]

Addendum:

The function "intersection" returns the number 0 and not a vector, if no common value is found.

This can be a problem if you nest that function to find the intersection of more than two vectors

To cope with this, add one line of code on top:

This should cure that problem

[RichardJ]

---

@IRarch wrote:

I changed it because we want to see the duplicate rows.

---

In the table there are no duplicate rows (I take your word for that; I have not looked). I strip the duplicate rows from the vectors because the vectors are used to fill the listboxes, and there's no point in having duplicate entries in the listboxes. That function is designed to strip duplicate rows from matrices, and using it for vectors is kind of overkill, but I already had it. You should not change it. If you want to see the duplicate rows, just don't remove them in the first place!

[Werner_E]

I think his intention was to misuse your routine for a different purpose. He is not using your scripted components which filter the possible values from his data list and have to avoid duplicates. He is using web controls with hard coded entries. So he does not need your routines to eliminate the duplicates.

He has only three of his four or five input values implemented until now and so there will be more than one row where all those three values match.

And I guess he simply wanted a routine which shows the indexes of those rows and he thought, if he simply changes the "not equal" to "equal" in your function, he would get that.

My "intercept" routine maybe would do what he tried to achieve (if my crystal ball worked OK)

[IRarch]

Hello @Werner_E @RichardJ

Please see the solution so far.

A problem I am encountering is that the option of a 12in Width and 11ft element does not happen but Z0 is telling me that on the 3^rd row those two happen when in the table provided are not. 

The option of 12in Width and 79.5in Height does give me the right output in USE. What I would expect with the 12in and 11ft.

Please let me know what you think about that problem…

[Werner_E]

You modified a routine which was intended to do something completely different. I don't understand why you think it should do whatever you think it should do!?

Your sheet is very hard to read and forces to scroll left right all the time so I have not bothered looking for the various routines you are using. It looks like the "intersection" I posted before does the job.

BTW, you may consider redefining the built-in "match" function at the top your sheet so it does not fail if no match is encountered.

If you chose "2x Sill Plate" in your sheet your whole sheet fails because in your table its called just "2x Sill".

It may have been a better idea to stick with Richards way of collecting the entries for the drop down menues from the data table rather than manually hard code them in a web control.

[RichardJ]

1) The way you have laid out the worksheet makes it very hard to debug. You obviously know that the worksheet calculates top left to bottom right, but in your worksheet it's really hard to figure out which expressions are before or after other expressions. If you have an expression way off to the right that is misplaced my one line up or down, it will execute either before or after an expression at the far left. You need to rearrange the worksheet so that it's mostly in the left column of pages. Why do you even want the current layout?

2) I don't know what you think the KeepDuplicateRows function does, but I can almost guarantee it does not do it. The StripDuplicateRows function was written for a specific purpose, and operates in a very specific way by sorting rows. If you just replace the one operator it would be remarkable if the function did anything useful at all, let alone what you want.

3) What do you want KeepDuplicateRows to do? Werner has made an educated guess, but you have neither confirmed nor denied whether he is correct.

[RichardJ]

---

@IRarch wrote:
PS. I changed the StripDuplicateRows program where I made the submatrix (=) instead of (not equal).

---

Why did you change it? If you change it then it will not work properly.

[Werner_E]

I guess Richard already did the job.

Please note that in your Excel sheet in the first column (Width) some entries like 93 3/4 are numerical (formatted as fraction), but others, like 92 1/4 are simply text!. If you read in the data via READEXCEL, this is causing troubles (I know, I was working on a similatr sheet Richard provided).

Richard was copying the Excel data directly to a Mathcad table and this seems to convert the text 92 1/4 automatically to a number, Drawback is that you can't just change the Excel table, you would have to copy the data again to the Mathcad table. Benefit is that the Mathcad sheet is self contained and does not need the Excel sheet anymore.

[IRarch]

Hi @Werner_E

Once more thanks! I am fixing this right now...

[RichardJ]

Like this?