Hello, I'm getting a #REF! error with your formula (my original message and
your response is below. I have assumed that Patterns is a name which refers
to the entire column of data containing the patterns. Also, that Dimension1
is the array containing the data of interest - that is the entire data from
the top left cell to bottom right. Is this correct? Does there have to be a
1 to 1 correspondence between each row on the new worksheet and the one
containing the data. If so, that would
mean there would be blank rows in the new worksheet as the corresponding
data sheet would not have the patten specified in the formula. Many thanks
for your help, David

-----------------------------------------
Your response to my question:

Create a PIVOT table is the best solution

If you prefered the formula then try this:

Sheet2

In A1:
=IF(ISERR(SMALL(IF(Patterns="Lines",ROW(INDIRECT(" 1:"&ROWS(Patterns)))),ROWS($1:1))),"",INDEX(Dimens ion1,SMALL(IF(Patterns="Lines",ROW(INDIRECT("1:"&R OWS(Patterns)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle from A1 all the way down as far as needed

Copy from A1 to B1 and change Dimension1 to Dimension2
Drag the Fill Handle from B2 all the way down as far as needed



"DavidS" wrote:

Hello, I have 8 columns of data that extends to over 1000 rows. One column
contains different patterns. I would like to copy the data in the cells of
two columns on the same row for all the instances of the same pattern to
another worksheet. I would then have a worksheet for each pattern. I may
not
have described this clearly so there an example below. Many thanks, David

Before

Column C Column F Column G
Cross 5.32 17.6
Lines 8.67 12.4
Circles 3.44 67.5
Lines 5.67 45.3
Squares 8.42 4.56
Lines 8.99 12.7
etc...

After - for the Lines pattern (another worksheet)

Column A Column B
8.67 12.4
5.67 45.3
8.99 12.7