

Thread Tools  Display Modes 
#1




Extract a matrix of another matrix defined by selected rows marked with an x
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 Last edited by Pecoflyer; 03192019 at 12:15 AM. Reason: wrong Language: selected lines from a matrix 
#2




Like the attached?

#3




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




I am not sure what you mean.

#5




Maybe something like this!

#6




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




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




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




Quote:
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 
#10




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




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




Quote:
This doesn't help me. I could not use the resulting structure for the work I want to do. Thanks for the trial... Tommy7571 
#13




Quote:
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 
#14




Quote:
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:
Code:
=IF(tSelection[@Field1]="";"";VLOOKUP(tSelection[@Field1];tSelection;2;0)) , but I'd prefer =IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"") 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:
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. 
#15




Extract a matrix of another matrix defined by selected rows marked with an x
<<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)) <<, but I'd prefer Code:
=IFERROR(INDEX(tSelection[Field2];tSelection[@RowNo]);"") 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.huegemanninformatik.de/...exceltabellen 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 
Tags 
matrix, sverweis 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Zeilen vergleichen und Meldung ausgeben  Bravo  Excel  1  02232018 06:31 AM 
2nd matrix gives a resut that doesn´t match numbers in the 1st matrix  Diver  Excel  25  02222017 04:39 PM 
How to get value through a matrix?  Xemx  Excel  3  10212014 05:46 AM 
Dot Matrix Printing  Angela  Office  2  05152012 08:56 PM 
Matrix Algebra  judicial85  Excel  3  10122011 05:41 PM 