two formulas needed

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'll try my best to explain this:
First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then return a 0.
Second formula needed in Sheet3 cell A1: Copy all the rows of Sheet1 that have a 1 in A:A. Preferably with no empty rows in Sheet3, just the data list.

This is driving me nuts, so any help is really appreciated.
Cheers.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
For part1, try
=--(Sheet2!B2=B2)

for part 2 have a look at advanced filters.
 
Upvote 0
This may require CTRL-SHIFT-ENTER and a change in the ranges and sheet names:

=IFERROR(INDEX(Sheet1!$A$2:$A$10,SMALL(IF(Sheet1!$A$2:$A$10=Sheet2!$A$2:$A$10,IF(Sheet1!$A$2:$A$10<>"",ROW(Sheet1!$A$2:$A$10)-1)),ROWS($A$1:A1))),"")
 
Upvote 0
First part worked great. :)
Thanks!
I'll see if I can figure the advanced filter out.
 
Upvote 0
How do I tell the advanced filter to only bring across the rows that have a 1 in Sheet1!A:A, and leave the 0's behind?
 
Upvote 0
Regarding your PM. Just type the formula in Sheet3!A1 and press CTRL then holding it down press SHIFT then holding both down press ENTER. Drag down until you get blank cells. You need to extend the ranges to suit your data. For example if your data in Sheet1 goes down to row 100 change the parts like this:

Sheet1!$A$2:$A$10

to this:

Sheet1!$A$2:$A$100
 
Upvote 0
When I did the ctrl+shift+enter and dragged the rows down, all it did was put a bracket at the beginning and at the end of the formula
 

Attachments

  • report.JPG
    report.JPG
    73.3 KB · Views: 8
Upvote 0
The brackets are as expected. You have a part wrong. Dash!$A$2:$A$2 needs to be Dash!$A$2:$A$10
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

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