How do I copy a row depending on the content of a cell in the row

[Guest]

I have a report that has in column A codes for different facilities. I would
like to create worksheet in the workbook that copy the contents of the rows
from the master worksheet depending on the contents of the cell in Column A.
For example
A B C D
SFMC 17 22 44
MAMC 12 13 37
SFMC 19 30 70

From this report on worsheet 1 would like to have a formula in worksheet 2
that will copy the contents of any row in worksheet 1 that has SFMC in column
A.
I have tried the IF statement but I can only get it to copy the contents of
column A not the entire row.
thanks for any advice!
Bob

 

[vezerid]

Assuming your data are in Sheet1!A24. Target code in Sheet2!F1.
Let us say you start your tabulation in Sheet2!A2. Then, in this cell
(*array* formula - commit with Shift+Ctrl+Enter):

=IF(COUNTIF(Sheet1!$A$2:$A$4,$F$1)>=ROWS($A$2:A2),SMALL(IF(Sheet1!$A
$2:$A$4=$F$1,ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1),ROWS($A
$2:A2)),"")

This is an auxiliary column, you can hide it if necessary. In B2:

=IF(A2="","",INDEX(Sheet1!$A$2:$D$4,$A2,COLUMNS($B$1:B1)))

Copy the B2 formula down and across to suit.

HTH
Kostis Vezerides

 

[Guest]

ColA, ColB, ColC, and ColD are defined name ranges

In Sheet 2

B2:
=IF(ISERR(SMALL(IF(ColA=$A$2,ROW(INDIRECT("1:"&ROWS(ColA)))),ROWS($1:1))),"",INDEX(ColB,SMALL(IF(ColA=$A$2,ROW(INDIRECT("1:"&ROWS(ColA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy across from B2 to D2
In C2: change ColB to ColC (cse)
In D2: change ColB to ColD (cse)
select B22 and copy down as far as needed