# two formulas needed

#### SplatP1

##### Board Regular
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

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
For part1, try
=--(Sheet2!B2=B2)

for part 2 have a look at advanced filters.

#### steve the fish

##### Well-known Member
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
First part worked great.
Thanks!
I'll see if I can figure the advanced filter out.

You're welcome.

#### SplatP1

##### Board Regular
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
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
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

• 73.3 KB Views: 6

#### steve the fish

##### Well-known Member
The brackets are as expected. You have a part wrong. Dash!\$A\$2:\$A\$2 needs to be Dash!\$A\$2:\$A\$10

still the same

#### Attachments

• 68.1 KB Views: 6

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