Copying filtered data to another worksheet

[DavidS]

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

 

[Teethless mama]

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(Dimension1,SMALL(IF(Patterns="Lines",ROW(INDIRECT("1:"&ROWS(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

 

[Debra Dalgleish]

You can do this with an Advanced Filter. There's a sample file here that
uses programming to create separate sheets. You could adapt that to your
workbook:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0009 - Update Sheets from Master'

 

[DavidS]

Thank you very much for your help. This really moves me forward.

 

[DavidS]

I need to ask for you help again. I'm getting a #REF! error. This is likely
to be because I don't fully understand the forumula. 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. Thank you for
your help, David