Lookup in 3D space but against ranges of values

[CivilPengineer]

My problem relates to a computer model of a building.

I have thousands of nodes output from the model each of which has stress values associated with it. Each nodes has an X, Y, Z value describing it's location in 3D space.

The strength properties of the building are assigned in hundreds of rows. Each row corresponds to a range of X, Y and Z values. For example, x between 0 and 50, y between <x<50, 1<y<13,="" -12<z<-10="" might="" describe="" a="" wall="" with="" certain="" strength,="" whereas="" 0<x<50,="" 13<y<20,="" the="" above="" which="" is="" weaker.
0 and 5 and z between 4 and 5 could describe a wall in the building with a certain strength property. x between 0 and 50, y between 5 and 10 and z between 4 and 5 could describe the wall above which would have a different strength value.

What I am trying to do is, for each node, find the associated strength value at that location, by automatically finding which set of ranges the node X,Y,Z values fall into and copying the associated strength properties to that node's row.

I'm hoping to do this without using macros, as it is easier for my work to be checked if I can stick to formulae.

Any ideas would be much appreciated!</x<50,>

 

[CivilPengineer]

I have had some success using match functions and IF functions with the array type (CTRL-SHIFT-ENTER). The problem is, I was using the lower bound values of each range, then matching the node values against the lower bound values - i.e. to find the next lowest value I have used [match type =1]. This works on a simple test sheet where I had entered dummy data in ascending order - but in reality my data is not arranged in ascending order.

 

[Gerald Higgins]

Hi, welcome to the board.

Can you give us a small sample of your data, together with an example of what exactly you want to do ? Don't worry too much about technical explanation of what the data represents, focus on the numbers and how you want to identify the ranges etc.

 

[CivilPengineer]

I wasn't sure how best to share this so I took a pic

I want to populate the black box on the left with the correct values of "Placeholder.." from the right, as looked up against the ranges of data on the right.

The ranges will not always be in ascending order, though they are here for X and Y.

 

[Gerald Higgins]

Sorry, I can't see your picture (I think because of workplace restrictions affecting my end), I'll look back later this evening at home. Unless you can describe your data in the text of a post ?

 

[CivilPengineer]

Ok lets's try typing out something - apologies it has turned out quite hard to read. I'm new to these forums so I don't know the best way to present stuff like this!

Node Co-ordinates.... Result
A1 - X B1 - Y C1 - Z D1 - Data
A2 - 4 B2 - 7 C2 - 11 D2 - (TBC)
A3 - 6 B3 - 2 C3 - 8 D3 - (TBC)
A4 - 45 B4 - 9 C4 - 11 D4 - (TBC)
A5 - 30 B5 - 3 C5 - 12 D5 - (TBC)
A6 - 15 B6 - 8 C6 - 8 D6 - (TBC)

Lookup Data ranges
E1 - MinX F1 - MaxX G1 - MinY H1 - MaxY I1 - MinZ J1 - MaxZ K1 - "Strength Data"
E2 - 0 F2 - 50 G2 - 2 H2 - 5 I2 - 8 J2 - 10 K2 - "PlaceholderA"
E3 - 0 F3 - 50 G3 - 6 H3 - 10 I3 - 10 J3 - 12 K3 - "PlaceholderB"

So for the first node (see row 2) (x=4, y= 7, z=11) this falls into the second set of ranges (X is 0 to 50, Y is 6 to 10, z is 10 to 12) and the value "PlaceholderB" would be brought across to cell D2.

For the node in row 3 (x = 6, y= 2, Z= 8) this is in the first set of ranges (X is 0 to 50, Y is 2 to 5, z is 8 to 10) and the value "PlaceholderA" would be brought across to cell D3.

For the node in row 4 (x=45,y=9, z=11) this again falls into the second set of ranges so "PlaceholderB" would be brought across to to cell D4.

For the node in row 5 (x=30,y=3,z=12) this does not fall into either set of ranges and therefore no value is brought across.

And so on.

As I mentioned, there are thousands of nodes and hundreds of sets of ranges to match them against, this is a massively simplified example! Any manual setting of ranges is no good, the spreadsheet needs to identify them itself.

Thanks in advance for your help!

 

[Gerald Higgins]

OK I'm at home now, I can see the image in post #4 fine, and I think I understand what you want to do.
It's a bit confusing that the data you've put in post #6 is different from post #4, I'm going to work with post #4.

I think you are saying that row 8, X=4, Y=4, Z=3, needs to say "placeholder B".
I've specifically chosen row 8, because several other rows don't appear to have full matches in the placeholder list, such as row 3. I'm guessing this is because the placeholder list is not shown in its complete version.

Have I got this right ?

 

[Gerald Higgins]

ALSO, is it an option for you to sort your look up table of MAX or MIN values ?
For example, select the range K3:Q12, apply Data Sort, and use either the MAX columns or the MIN columns as the 1st, 2nd and 3rd sort keys ?
If you can do this, your problem should be solve-able quite easily.

 

[CivilPengineer]

OK I'm at home now, I can see the image in post #4 fine, and I think I understand what you want to do.
It's a bit confusing that the data you've put in post #6 is different from post #4, I'm going to work with post #4.

I think you are saying that row 8, X=4, Y=4, Z=3, needs to say "placeholder B".
I've specifically chosen row 8, because several other rows don't appear to have full matches in the placeholder list, such as row 3. I'm guessing this is because the placeholder list is not shown in its complete version.

Have I got this right ?

Thanks for looking at this!

OK, yes ignore post 6.

Yes, the data I have entered is just a simplified example (not the actual data). There should be more matches in the real data! However, there may be some nodes which don't have a match in the final data and I do want to catch these.

Anyway, you are right that row 8 should return "PlaceholderB", row 15 will return "PlaceholderH" and so on.

As for sorting - it might be possible but due to the format of the strength data tables I have, it would take quite a lot of formatting to get rid of merged cells etc. I will have a look at sorting a sample, once I have done this I will be able to figure out if it's realistic to do the whole lot.

Thanks again

Luke

 

[CivilPengineer]

I have tried reformatting the lookup info to get rid of merged cells etc and then sorting it to be in ascending order, for the sample I am working on. Doing the whole data set should take a few days. It's not a huge amount of time, but obviously if anyone can figure out how to do the lookup without sorting the data into ascending order, I would be grateful. If not I guess I will just have to put in the hours!

Thanks

Luke