Find all data with 2 criteria

[Mega1]

looking to fine all data that matches 2 criteria and put in sheet2.
The data is made up of number company % total Tax and a y

I want to find a company and all the "y" it has

 

[Mega1]

Sheet 1 looks like
[TABLE="width: 867"]
<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Product[/TD]
[TD]Company[/TD]
[TD]Terms %[/TD]
[TD]Grose[/TD]
[TD]Tax%[/TD]
[TD]Invoice[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2530[/TD]
[TD]Assassin's Creed(1)[/TD]
[TD]FOX INT'L[/TD]
[TD="align: right"]0.55[/TD]
[TD="align: right"]1807.5[/TD]
[TD="align: right"]209.7695[/TD]
[TD="align: right"]1121.811[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2531[/TD]
[TD]Ballerina(3)[/TD]
[TD]EONE FILMS[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]1173[/TD]
[TD="align: right"]86.62982[/TD]
[TD="align: right"]463.2812[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2532[/TD]
[TD]Collateral Beauty(2)[/TD]
[TD]WARNER BROS INT'L[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]603[/TD]
[TD="align: right"]50.89541[/TD]
[TD="align: right"]272.1798[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD="align: right"]2522[/TD]
[TD]Fantastic Beasts and where to Fine Them(8)[/TD]
[TD]WARNER BROS INT'L[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]535.5[/TD]
[TD="align: right"]39.54839[/TD]
[TD="align: right"]211.4979[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD="align: right"]2533[/TD]
[TD]La La Land(-1)[/TD]
[TD]LIONSGATE[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]67.88991[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2524[/TD]
[TD]Moana(6)[/TD]
[TD]WALT DISNEY INT'L[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1862[/TD]
[TD="align: right"]196.4495[/TD]
[TD="align: right"]1050.578[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD="align: right"]2534[/TD]
[TD]Monster Trucks(2)[/TD]
[TD]PARAMOUNT INT'L[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]1408.5[/TD]
[TD="align: right"]118.8826[/TD]
[TD="align: right"]635.7633[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2535[/TD]
[TD]Passengers(3)[/TD]
[TD]SONY INT'L[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1125.5[/TD]
[TD="align: right"]118.7454[/TD]
[TD="align: right"]635.0298[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2536[/TD]
[TD]Rogue One:Star Wars(4)[/TD]
[TD]WALT DISNEY INT'L[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1265.5[/TD]
[TD="align: right"]133.5161[/TD]
[TD="align: right"]714.0206[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2537[/TD]
[TD]Why Him?(2)[/TD]
[TD]FOX INT'L[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1843[/TD]
[TD="align: right"]194.445[/TD]
[TD="align: right"]1039.858[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need on sheet 2 everything that has a y in column h for a chosen company

so if I pick warner I should get 2 rows in sheet 2

 

[JoeMo]

Have you considered filtering on the Company column to show only Warner, then on col H for "y"? This would give you two rows below the header row which you can copy and paste to Sheet2.

 

[Mega1]

no I cant to that I would have to do that for every week then when I change to another company I would have to do it again

 

[Aladin Akyurek]

A1 of Sheet2 houses a company of interest like WARNER.

In A2 just enter:

=COUNTIFS(Sheet1!C2:C2000,"*"&A1&"*")

In A3 enter the string Idx, B3:I3 enter the headers from Sheet1 as is.

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$C$2:$C$2000)),IF(Sheet1!$H$2:$H$2000="y",ROW(Sheet1!$C$2:$C$200)-ROW(Sheet1!$C$2)+1)),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$H$2000,$A4,MATCH(B$3,Sheet1!$A$1:$H$1,0)))

 

[Mega1]

this only worked for the first row
cell a2 came back with 78
in a4 it came back with 3 which when I looked in sheet1 the first one that matches is the third row under the heading
in a5 3 came again where I though it should be 4
in b4 was the company name I was looking for and all the other info came in as well but not the last one it says #N/A
in b5 is #N/A

 

[Aladin Akyurek]

Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[Mega1]

that has been done

 

[Mega1]

[TABLE="width: 780"]
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Movie Company[/TD]
[TD][/TD]
[TD]Payable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WARNER BROS INT'L[/TD]
[TD][/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COMPANY[/TD]
[TD]TERMS[/TD]
[TD]GROSS[/TD]
[TD]PEOPLE[/TD]
[TD]INVOICE[/TD]
[TD]9%[/TD]
[TD]Net[/TD]
[TD]Dis Sub Total[/TD]
[TD]23%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]WARNER BROS INT'L[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]603[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]272.1798[/TD]
[TD="align: right"]49.78899[/TD]
[TD="align: right"]553.211[/TD]
[TD="align: right"]221.2844[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]837[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]839[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]869[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]898[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]901[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]927[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]958[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]984[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]988[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
this is what it looks like

 

[Aladin Akyurek]

See the workbook which implements the suggestion: https://www.dropbox.com/s/ko3fqpzl92zwkx5/Mega1 sublist.xlsx?dl=0

If you want WARNER BROS INT'L<strike></strike> instead of just WARNER in A1, modify the formulas as follows:

A2 >>

=COUNTIFS(Sheet1!C2:C2000,A1)

A4 >> control+shift+enter

<strike></strike>=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$C$2:$C$2000=$A$1,IF(Sheet1!$H$2:$H$2000="y",ROW(Sheet1!$C$2:$C$200)-ROW(Sheet1!$C$2)+1)),ROWS($A$4:A4)))<strike></strike>