M

#### MCSmarties

I need to retrieve data matching particular criteria from a worksheet.

All "matching" cells should be inserted in a new worksheet

(essentially a "cleaned" sheet containing ONLY the relevant data)

All cells that do not match the criteria should be "skipped"

completely, NOT just left blank.

It needs to be done automatically, not requiring any user interaction

(so data sorting won't work)

Example: I have data in the following form in Worksheet 1

A x1 x2 x3 x4

B y1 y2 y3 y4

C x1 x2 x3 x4

D z1 z2 z3 z4

E x1 x2 x3 x4

F u1 u2 u3 u4

I'm only interested in the data of type "x" (so only rows A, C, E

match).

I want Worksheet 2 to contain ONLY this relevant data:

A x1 x2 x3 x4

C x1 x2 x3 x4

E x1 x2 x3 x4

eg WITHOUT empty cells for the "non-matching" data.

If Worksheet 1 is modified, Worksheet 2 should reflect the changes

automatically

(for example if the data in row C is replaced with data of type "w",

Worksheet 2 should

only contain:

A x1 x2 x3 x4

E x1 x2 x3 x4

Skipping individual cells would be even better! eg:

Worksheet 1:

A x1 y2 x3 x4

B y1 x2 x3 y4

C y1 y2 y3 y4

to

Worksheet 2:

A x1 x3 x4

B x2 x3

Can this be done, ideally while only using formulae?

Execution speed is not really an issue.

( I used rows for this example, data could also be in columns)