## Copy and grouping all rows that meet the requirement that is repeated in Excel

In this tutorial I will try to show how we can by using the formula to copy all the data in certain non-adjacent rows that contain a certain condition, whether the data is located on the same worksheet or to another worksheet. In the picture below you see 'Sheet1' on it and the table data. I painted the data in rows that are related to a specific date. Notice that I have three unique date repetitive. Precisely, the date will be the condition on which we want to copy rows to another worksheet.

## Copy and group Rows if satisfied a condition that repeats

## (Copy and group Data if match condition)

In the picture below you can notice the result ie. the copied rows from 'Sheet1' to 'Sheet2', which meets the requirement of the cells 'B1'. So we layperson can say the following. Copy all rows from 'Sheet1' containing condition ie. The date of the cells 'B1' and

All ARRAY formulas which are below said return the same result. The formula is placed in a cell 'A5' to 'Sheet2' and copied to the right and then down.

NOTE: The formulas in this tutorial may not be in one row because they take too long and they do not fit in the box website. If you copy them to your Excel file, I suggest copying them in Notepad and configure them to be in one line and then copy to Excel.

=IF(ISERROR(INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));ROW(1:1));COLUMN(A1)));"";INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));ROW(1:1));COLUMN(A1)))

=IFERROR(INDEX(Sheet1!$A$2:$E$100;SMALL(IF(Sheet1!$A$2:$E$100=$B$1;ROW(Sheet1!$A$2:$E$100)-ROW(Sheet1!$A$2)+1;ROW (Sheet1!$A100)+1);

In the following two formulas, which seem to look the same, but not the same. You notice that a different range of the original data entered it depends on whether we in the formula set subtraction number of rows, which indicates to us which row is the first data of our range (marked in blue)

=IFERROR(INDEX(Sheet1!$A$2:$E$100;SMALL(IF(Sheet1!$A$2:$E$100=$B$1;ROW(Sheet1!$A$2:$E$100));

=IFERROR(INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));

In these formulas were used certain functions, instead of which you can use a number or a different shape functions.

For example follows:

instead COLUMN(A1), you can use the number '1'

instead COLUMN(B1) you can use the number '2'

instead ROW(A1), you can use the number of ROW(1:1)

instead ROW(A2), you can use the number of ROW(2:2)

This is important when copying.

For example: If you use the number '1' instead COLUMN(A1) then when copying to the right, this number will not change and you need to manually change (in 2, 3, 4, etc.) in the other columns in which you expect the result because it is tied to column of the original data.

But, if you use the COLUMN (A1) then when copying to the right, the formula works because it will change the A1 and A2, A3, A4, etc.

Of course, as always I suggest naming the range of source data, an appropriate name, because the formula will be shorter and easier to work with her. In addition, if you are named cell ranges then we do not need to use an absolute address range.

For example: If you are named the absolute range of data 'Sheet1!$A$1:$E$100' name "base" then the formula could look like this

=IFERROR(INDEX(base;SMALL(IF(base=$B$1;ROW(base)-ROW(Sheet1!$A$2)+1;ROW(Sheet1!$A100)+1);

or this formula bellow in which you need manually change number of the

the data range Sheet1!A2:E100 = name range of data 'base'. Without header of Columns

=IFERROR(INDEX(base;SMALL(IF(base=$B$1;ROW(base));

IFERROR function prevents the occurrence of errors in Excel if the Row does not meet the requirement. In Excel 2003, you can use a combination of Excel function IF/ISERROR

**group them without empty rows**.All ARRAY formulas which are below said return the same result. The formula is placed in a cell 'A5' to 'Sheet2' and copied to the right and then down.

NOTE: The formulas in this tutorial may not be in one row because they take too long and they do not fit in the box website. If you copy them to your Excel file, I suggest copying them in Notepad and configure them to be in one line and then copy to Excel.

**Formula for Excel 2003**=IF(ISERROR(INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));ROW(1:1));COLUMN(A1)));"";INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));ROW(1:1));COLUMN(A1)))

**Formulas for Excel 2007, 2010, 2013**=IFERROR(INDEX(Sheet1!$A$2:$E$100;SMALL(IF(Sheet1!$A$2:$E$100=$B$1;ROW(Sheet1!$A$2:$E$100)-ROW(Sheet1!$A$2)+1;ROW (Sheet1!$A100)+1);

**ROW(A1)**);**COLUMN(A1)**);"")In the following two formulas, which seem to look the same, but not the same. You notice that a different range of the original data entered it depends on whether we in the formula set subtraction number of rows, which indicates to us which row is the first data of our range (marked in blue)

=IFERROR(INDEX(Sheet1!$A$2:$E$100;SMALL(IF(Sheet1!$A$2:$E$100=$B$1;ROW(Sheet1!$A$2:$E$100));

**ROW(1:1)**)-1;**COLUMN(A1)**);"")=IFERROR(INDEX(Sheet1!$A$1:$E$100;SMALL(IF(Sheet1!$A$1:$E$100=$B$1;ROW(Sheet1!$A$1:$E$100));

**ROW(1:1)**);**COLUMN(A1)**);"")In these formulas were used certain functions, instead of which you can use a number or a different shape functions.

For example follows:

instead COLUMN(A1), you can use the number '1'

instead COLUMN(B1) you can use the number '2'

instead ROW(A1), you can use the number of ROW(1:1)

instead ROW(A2), you can use the number of ROW(2:2)

This is important when copying.

For example: If you use the number '1' instead COLUMN(A1) then when copying to the right, this number will not change and you need to manually change (in 2, 3, 4, etc.) in the other columns in which you expect the result because it is tied to column of the original data.

But, if you use the COLUMN (A1) then when copying to the right, the formula works because it will change the A1 and A2, A3, A4, etc.

Of course, as always I suggest naming the range of source data, an appropriate name, because the formula will be shorter and easier to work with her. In addition, if you are named cell ranges then we do not need to use an absolute address range.

For example: If you are named the absolute range of data 'Sheet1!$A$1:$E$100' name "base" then the formula could look like this

=IFERROR(INDEX(base;SMALL(IF(base=$B$1;ROW(base)-ROW(Sheet1!$A$2)+1;ROW(Sheet1!$A100)+1);

**ROW(A1)**);**COLUMN(A1)**);"")or this formula bellow in which you need manually change number of the

**Column**. In this formula, you notice that I used the named range data. In this case we have to subtract 1 rows in the formula. That follows**-1**, becausethe data range Sheet1!A2:E100 = name range of data 'base'. Without header of Columns

=IFERROR(INDEX(base;SMALL(IF(base=$B$1;ROW(base));

**ROW(1:1)**)**-1**;**1**));"")IFERROR function prevents the occurrence of errors in Excel if the Row does not meet the requirement. In Excel 2003, you can use a combination of Excel function IF/ISERROR

After put up the formula in cell 'A5' and after copying, you format cells the column 'A' as a date.

**Different situation, explanation formula**

In this second situation (see image below) first data is in the Row 6. For this explanation, I will use ARRAY formulas

=IFERROR(INDEX(Sheet1!

**$A$6:$E$100**;SMALL(IF(Sheet1!$A$6:$E$100=$B$1;ROW(Sheet1!$A$6:$E$100));ROW(1:1))

**-5**;COLUMN(A1));"")

or if you use a named range then ARRAY formula looks like this

=IFERROR(INDEX(

**base**;SMALL(IF(base=$B$1;ROW(base));ROW(1:1))

**-5**;COLUMN(A1));"")

Explanation ARRAY formulas above see in the picture below.

I hope you have understood this tutorial, and you find the above-mentioned formula to help resolve your Excel tasks