Generating a new list based on cell criteria

[JerryS]

column A has one of five different values with additional data in the cells
to the right. This is repeated over several tabs. I want, based on the value
in A, build a list showing all of the data for the row. For instance, if A1
is "Red", and this is repeated on A4, A7 and A9, then I want to create on
another tab, a list consisting of A4, A7, and A9 including the data in B4,
C4, D4 and so on. Any suggestions?

 

[Max]

Source data assumed in Sheet1, cols A to E, from row1 down
with the key col = col A

In Sheet2.
Assume A1 will house one of the 5 values, eg: Red
(you could use a simple DV to select the 5 values)

In B1:
=IF(Sheet1!A1="","",IF(Sheet1!A1=$A$1,ROW(),""))

In C1:
=IF(ROW()>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:$B,ROW())))
Copy C1 to G1. Select B1:G1, copy down to cover the max expected extent of
data in Sheet1. Minimize/hide away col B. Cols C to G will return the
required lines from Sheet1, all neatly bunched at the top.