Hello,

I have the following problem:
I have a table with data, where not always all cells shall be used, however the sequence must stay the same.
example:

Column 1 Column 2 Column 3 Column 4
x TBA 50 99
DTBP 230 99
x Azeton 50 99

In the first column a x decides if the values of the following Cells need to be used or not. Off Course the x can be in all lines or in none.
I need a virtual data matrix, which includes only marked cells and from which I can extract with VLOOKUP() or Similar the data of the activated cells.
The Matrix should only exist virtually in the formula and have the following form in the example given above



Column 1 Column 2 Column 3
TBA 50 99
Azeton 50 99

From this matrix I want to extract all data linewise.
Can anyone help me?

Thanks

Tommy7571

Like the attached?

Attached Files

Auswahl.xlsx (9.0 KB, 8 views)

Thanks very much, I did try to use matrix parenthesis and I found hints for such use but it did not work. Now at least I see how to work with matrix parenthesis.
Howevever, it is not fully what I wanted to obtain.
Can you merge the fields to obtain one matrix of all lines?
In principle I could also create a matrix in Excel (not virtual) to store the values but it needs to have in every cell a value like the one I showed in my posting as a result matrix. This is even the most important target, since there can be more than one line which is not used and needs to be excluded to use such values in other fields without problems...



Thanks


Tommy7571

I am not sure what you mean.

Maybe something like this!

Attached Files

Auswahl.xlsx (15.4 KB, 8 views)

From the Matrix (4x3)



[x] [TBA] [50.0][99.0]
[ ][DTBP][80.0][99,8]
[x][Azeton][100][99.0]


I want to extract the Matrix (3x2) as a function of the lines
marked with x



[TBA] [50.0][99.0]
[Azeton][100][99.0]


to have an entry in each row.


Thanks


Thommy7571

Hello,


yes this could be a solution. It is a solution if I store the matrix data in a physical matrix. Is it possible to have the corresponding matrix as a virtual matrix - without storing it in another physical matrix?
Can I access data by VLookUp() to get the values of each line?
If I try it I get an error "Value!" (as I already got plenty times without understanding the reason...). Can you tell me why?



Can you explain a little bit how it works?
You are using a filter function but the formulas are completely new to me...



Thanks

Attached Files

Auswahl2.xlsx (15.5 KB, 8 views)

Hi and welcome
could you please provide a thread title in English please, so that our other members looking for a similar solution can find their way?
Thank you

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Off course I already tried but it was not possible to edit the title.

"Extract a matrix of another matrix defined by selected rows marked with an x"


Can you edit it ?


Thanks


Tommy7571

I don't know if the attached can be of some inspiration.

Be aware however that it makes use of the function TEXTJOIN which I think is only available in the newest version of Excel.

Attached Files

Extract_Rows.xlsx (10.7 KB, 7 views)

It works in the file you sent me but not in the file I am working with.
I get only error message whatever I do...


Can you help me?


Tommy7571

This doesn't help me. I could not use the resulting structure for the work I want to do.


Thanks for the trial...


Tommy7571

Thank you - Done


And BTW, do not quote entire posts unnecessarily. If they are out of sequence, just quote what is necessary to make your post understandable. It makes threads easier to read

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What is the meaning of "matrix" in your context? I am not very proficient with german IT terminology (the last time I used it at some elementary level was some 15 years ago in german Excel and Access newsgroups), and I haven't never encountered such use of it!

Generally, on worksheet to refer to some data you must have those data displayed somewhere (in cell, or in table). You can have the data you refer to on hidden worksheet, or in hidden columns of table, but you must have them somewhere.

An exception are named dynamical values, where you create a name which value is calculated based on position of cell the named value is used.

And you can create your own UDF's (User Defined Function), which you can use instead of very complex formulas.

In any of those cases the final formula must read some data from some place in workbook, and must be entered into some cell.

In VBA procedures, you can define arrays (those can have many dimensions). But generally, those arrays exist only until the procedure is running. Probably it is possible define such arrays as global, but anyway you can access them only in VBA.

The syntax was wrong. It must be
VLOOKUP returns entry from 1st matching row. When there is several indentical entries in Field1, you are in trouble. The 2nd formula doesn't have such problems.

Parameters used in lookup are:
1. The value you are searching for in 1st column of search range;
2. The search range;
3. The number of column in search range from where the result is returned;
4. 0 is for exact match - i.e. formula returns an error when search value is not found. Other values for 4th parameter are more difficult to handle, and the search range must be ordered by search value.

I used Table formulas. With Table I mean the entity defined from menu Insert>Table. With defined Table you can use specific syntax to refer to various regions of Table.

Useful feature of Tables is, that whenever you enter something into any cell immediately below any cell of last row of Table, the Table is expanded automatically. And any formats, conditional formattings, data validations, or formulas are expanded too, until there is only one of kind defined for whole column.

<<Generally, on worksheet to refer to some data you must have those data <<displayed somewhere (in cell, or in table). You can have the data you refer to on <<hidden worksheet, or in hidden columns of table, but you must have them <<somewhere.


Ok. Anyway it is not a problem to create a hidden table somewhere and it is better. Perhaps I confued with programming techniques and I don't want to use these. So I will do it like this.


<<The syntax was wrong. It must be
Thanks very much I don't even understand why I wrote this formula like this. Perhaps because I am not very used to use the VLookUp -function...


<<, but I'd prefer
<VLOOKUP returns entry from 1st matching row. When there is several indentical <entries in Field1, you are in trouble. The 2nd formula doesn't have such problems.

To have the same entry two times is not reasonable in my case. As far as I know the iferror function is not recommended since it often gives an error without giving a hint on the error.



<I used Table formulas. With Table I mean the entity defined from menu <Insert>Table. With defined Table you can use specific syntax to refer to various <regions of Table. Useful feature of Tables is, that whenever you enter something <into any cell immediately below any cell of last row of Table, the Table is expanded <automatically. And any formats, conditional formattings, data validations, or <formulas are expanded too, until there is only one of kind defined for whole column.


Great! Thanks very much. I never used such type of tables and it is very interesting.


I established the same table in my excel sheet and put the filter function in each of the consiedered table columns. I created a range named "Analyte" in the name manager and even took the same names for the considered columns.

As in your table tData, I put the function on the right of the table in the column RowNo.
=ZEILE()-ZEILE($D$7)+1 works but

ZEILE()-ZEILE(Analyte[[#Kopfzeilen];[RowNo]]) doesn't :



I used the following article to study the method:



https://www.huegemann-informatik.de/...excel-tabellen


According to this article there should be no problem with my function...

However, I still get the same error. Hints are so general that they do not help...



the following code



has been put in the column SelectedRowNo. It also gives an error.
What is @Selected ? Where is it defined?
In the attachment I deleted = to avoid error messages.



I did not even try to work on the table selections...


Any idea where the problems could be since the tables of your excel file works...
I wasted hours for this and see no solution.



Thanks


Tommy7571

Attached Files

Test.xlsx (13.8 KB, 6 views)