cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Matching values in a row of a table

Highlighted
Regular Member

Matching values in a row of a table

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!

21 REPLIES 21

Re: Matching values in a row of a table

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 ?

Bild.png

Re: Matching values in a row of a table

Like this?

Re: Matching values in a row of a table

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!

Re: Matching values in a row of a table

Does this not do what you want?

Re: Matching values in a row of a table

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.

 

Re: Matching values in a row of a table

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.

Re: Matching values in a row of a table

Hi @Werner_E

 

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

Re: Matching values in a row of a table

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

Re: Matching values in a row of a table

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 :-(