I've seen some pretty helpful threads on excel problems here before so I'm hoping I can also use the forum to figure something out...
I have a spreadsheet containing a 29x3 array of element IDs, with a similar array containing their thicknesses and a third array containing their materials.
I'd like to consolidate this into a list with element Id in the first column, thicness in the second and material in the third.
I can easily generate a single list of the IDs, but then is there a function which allows me to look up a value from an array based on picking a cell in the same position as in another array (e.g can I figure out that a certain element is on the 2nd row in the fourth column in array 1, then look up the area from the 2nd row, fourth column in another array?)
Hopefully that makes some kind of sense...
Sounds like OFFSET and MATCH are what you need.
is there a particular reason why you have three 29x3 arrays rather three 87x1 arrays, or even better, one 87x3 array?
Separate tables are what I have inherited! Better for visualising the position of the elements compared to the structure they represent, but not helpful for generating the list I need!
Hopefully I'm not mixing my terminology...
To be clear, cells b4 to ad6 contain element IDs, cells b8 to ad10 contain thicknesses, b13 to ad15 materials.
Therefore if I want to return the thickness for a given element, I need to find its position within b4:ad6, then look for the thickness in the equivalent cell within b8:ad10..
Is this possible without macros? (I hate macros!)
fair enough, sounds like you at least have a description of the format which is half the battle.
no idea how to do it in excel though, easy in python/numpy 😉
Is this a one-off to combine the data? Could just copy and transpose each row since there aren't many (copy, right click paste options).
Not a one off unfortunately. The best I've come up with is 3 sets of index match within a nested ' if'. Not elegant, but since there are only 3 rows it's simple enough!
Is there a reason why you really want to do it in Excel? It can be done as you describe, but often its better to step back and Excel is only being used because it is the tool you already use for a bunch of (probably equally unsuitable) tasks.