Find all data with 2 criteria

M

Mega1

New Member
Joined
Sep 19, 2017
Messages
25
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
[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
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Automating a formula using a pivot table and vba
Replies
0
Views
301
K0D54
K
N
  • Question Question
Automating VB-Tax form reports
Replies
1
Views
65
Ndk
N
M
  • Question Question
Count number of contribution to top %
Replies
0
Views
124
mira2020
M
N
  • Solved
V-Lookup Issues
Replies
4
Views
179
dreid1011
dreid1011
J
  • Solved
Calculate sums based on values
Replies
3
Views
275
NateSC
NateSC

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top