Copy specific cells from one sheet to an other

[NNO123]

Hi guys,

I have a question, and would like to hear your thoughts

So in sheet 1, I have the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Priority
[/TD]
[/TR]
[TR]
[TD]company1[/TD]
[TD]information[/TD]
[TD]information[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]company2[/TD]
[TD]information[/TD]
[TD]Information[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]company3[/TD]
[TD]Information[/TD]
[TD]Information[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]company4[/TD]
[TD]Information[/TD]
[TD]Information[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

And this countinues down

Now I want to create a summary sheet, with the top priorities.
So if priority = 1, I want to copy the company name, data1 and data2 (just keep the formatting) into sheet 2.

So I could make some If-statements - for example =if("priority cell" = 1, Company1, "") and the same for data1, data2 and duplicate it to the next row - , but then I will have some space between my companies.
and it will look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Priority[/TD]
[/TR]
[TR]
[TD]company1[/TD]
[TD]information[/TD]
[TD]information[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]company 4[/TD]
[TD]information[/TD]
[TD]information[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I would like company 4 to be in the rows below company 1.

Do you think this is possible? I know it is possible to filter sheet 1 by the priorities and just copy all 1-priorities to sheet 2 - but do you think that the numbers can appear automatically in sheet 2?

Thanks a lot guys!!

 

[Special-K99]

Common problem.
Usual answer would be

in Sheet2!A1
=IFERROR(INDEX(Sheet1$A$1:$D$1000,SMALL(IF((Sheet1$D$1:$D1000=1),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$2)-1),COLUMN()),"")
Array formula, use Ctrl-Shift-Enter
copy across the sheet up to D1
copy the formula down for as many rows as you have on Sheet1

Since your output is identical to your input, ie 4 columns, you can use COLUMN() otherwsie you would need to replace the formula each time with the column number of whichever column you want to extract from Sheet1