Extract data from multiple tables

[BPP]

Hello everyone

I have to extract values from different tables, so according to the selected component, the required values are obtained. There is a way to do this automatically.

Attached is a sheet with what I intend to do

[Werner_E]

The data you need can easily be extracted by the use of "vlookup".

If you always need the combination of values give in your sheet, you would create a function to do this in one call.

I am not sure, though, how you would intend to generalize the problem as you write in your sheet "for example with two data tables".

[BPP]

Use the "vlookup" command is a good option, but the problem arises when you have multiple tables of data, and have to find that table contains the required value, which takes a long time, so I think the best option would create a function to do this.

[Werner_E]

... but the problem arises when you have multiple tables of data, and have to find that table contains the required value, which takes a long time ...

??? its unclear to me what you need. A program would not be able to find that one value which you require because it can't read your mind. You will have to specify in some way how to distinguish between the one value you require and the others in the same row.

[BPP]

Sorry I think I explain evil.

For example, for component "AB-1001", value desired is "G". Then the function created, search for in the front row of Data_1 if "G" exist, if so, to extract it, otherwise they search for in the first row of Data_2, and if not there at that table, that returns the value zero or NaN.

[Werner_E]

I'll try to summaraize what I have understood so far:

What you want is a function with three arguments:

• One argument is a component name, like "AB-101"
• a second argument is a column descriptor like the "A", "B", ,,,, etc. you see in your Excel components
• the last argument is a list (vector) consisting of an arbitrary number of tables. If we could assume that all tables have the same number of rows and the very same row headers in the very same order, you could also provide those tables by using augment() to make for one big table, but I am not sure if we may assume that restrictions.

There are some problems now:

1) You do not provide column headers now, so Mathcad does not see the "A", "B". ,, etc. So have to change the output of your Excel component to include row number 1 as well. Is there any reason you use those Excel components as opposed to provide the data directly and get rid of the annoying Excel alltogether?

2) There is no way you can convert a string to a variable name or vice versa. You have to manually make the connection that variable A gets the value associated with label "A".

Given that you include the column headers in your data tables, it sure would be quite easy to create a function which can be used like so:

but from looking at your sheet I am absolutely if this is what you are searching for.

EDIT: How should the case be handled, that two tables have the very same column header, lets say "G" ?

[RichardJ · Accepted solution]

Does this do what you want?

[BPP]

Has every reason Werner, I have made many mistakes in the formulation of the question by not including the headers of the components in Excel, and not put as a string to the desired variables "A", etc. What I was trying to achieve is precisely what you and Richard displayed with the function "get_value".

Thank you very much to you and Richard for taking the time to help me and sorry for generating misunderstandings.

[zhulaojianke]

Mine solution:

I have read the http://communities.ptc.com/message/268914 , and I think "BBP" needs this function.

[Werner_E]

I have read the http://communities.ptc.com/message/268914 , and I think "BBP" needs this function.

Or something like the attached which I had created but Richard was faster 😉

The main problem Bryan had, as I understood, was that he has multiple tables to pick values from.

Anyway - he seems to be happy with Richard's solution.

BTW, if you want to make your approach ORIGIN-aware (and I think you want as you used ORIGIN+1), you should not use index zero in match() or column number "cols(y)-1" in submatrix().

[zhulaojianke]

Wow, it is a good flexible method!