two formulas needed

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
66
Office Version
365
Platform
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
For part1, try
=--(Sheet2!B2=B2)

for part 2 have a look at advanced filters.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
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))),"")
 

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
66
Office Version
365
Platform
Windows
First part worked great. :)
Thanks!
I'll see if I can figure the advanced filter out.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
You're welcome.
 

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
66
Office Version
365
Platform
Windows
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?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
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
 

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
66
Office Version
365
Platform
Windows
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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
The brackets are as expected. You have a part wrong. Dash!$A$2:$A$2 needs to be Dash!$A$2:$A$10
 

Watch MrExcel Video

Forum statistics

Threads
1,095,267
Messages
5,443,432
Members
405,235
Latest member
1Thess521

This Week's Hot Topics

Top