View original (French)

Extracting row to new sheet based on criteria

emdo31 Posted messages 6 Status Member -  
via55 Posted messages 14393 Registration date   Status Member Last intervention   -
Hello,

I would like to know if it is possible, using a simple formula without resorting to a macro, to automatically extract rows from several different sheets (according to a defined criterion) and combine them into a new one?
Let me explain, I have several tabs where I collect data in a table with several columns, and the last column displays a score. I would like to extract all the rows from the different sheets where the score is greater than "12".
This would allow me to track part of the data more easily without juggling between the different tabs...

I am not sure I can achieve this result without using a macro, but if that is the case, it is beyond my skill set...

Thank you in advance for your feedback!

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    The formula:
    =IFERROR(INDEX('Process 1'!H:H,MATCH(ROW()-1,'Process 1'!$I:$I,0)),IFERROR(INDEX('Process 2'!H:H,MATCH(ROW()-1,'Process 2'!$I:$I,0),""))

    INDEX('Process 1'!H:H,MATCH(ROW()-1,'Process 1'!$I:$I,0)
    INDEX extracts from column H of Process 1 the value located on the same row as the row of the column containing the value ROW()-1, meaning the current row number -1. In row 2, it searches for value 2, in row 3 it looks for 2, etc.
    Thus, it sequentially retrieves the items from Process 1 that correspond to the criterion
    with IFERROR to handle cases where no match is found; here for example, if 3 does not exist in Process 1, it conducts the same search in Process 2 and the IFERROR before this second INDEX returns empty (the "" at the end of the formula) if nothing is found in Process 2
    --
    "Imagination is more important than knowledge." A. Einstein
    1