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

ArviLaanemets · #1 03-18-2019, 08:04 AM

Maybe something like this!

Tommy7571 · #2 03-18-2019, 08:22 AM

Quote:

Originally Posted by ArviLaanemets

Maybe something like this!

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

ArviLaanemets · #3 03-19-2019, 12:39 AM

Quote:

Originally Posted by Tommy7571

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?

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.

Quote:

Originally Posted by Tommy7571

Can I access data by VLookUp() ... If I try it I get an error "Value!"... Can you tell me why?

The syntax was wrong. It must be

Code:

=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0))
, but I'd prefer
=IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"")

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.

Quote:

Originally Posted by Tommy7571

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

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.

Tommy7571 · #4 03-19-2019, 05:12 AM

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

Code:

=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0))

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

Code:

=IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"")

<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

Code:

=WENN([@Selected]="x";ZÄHLENWENNS([Selected];"x";[RowNo];"<=" & [@RowNo]);"")

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

Tommy7571 · #5 03-19-2019, 05:13 AM

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

Code:

=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0))

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

Code:

=IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"")

<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.

Code:

=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

Code:

=WENN([@Selected]="x";ZÄHLENWENNS([Selected];"x";[RowNo];"<=" & [@RowNo]);"")

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

Tommy7571 · #6 03-18-2019, 01:06 PM

Quote:

Originally Posted by ArviLaanemets

Maybe something like this!

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Zeilen vergleichen und Meldung ausgeben	Bravo	Excel	1	02-23-2018 06:31 AM
2nd matrix gives a resut that doesn´t match numbers in the 1st matrix	Diver	Excel	25	02-22-2017 04:39 PM
How to get value through a matrix?	Xemx	Excel	3	10-21-2014 05:46 AM
Dot Matrix Printing	Angela	Office	2	05-15-2012 08:56 PM
Matrix Algebra	judicial85	Excel	3	10-12-2011 05:41 PM