two formulas needed

[SplatP1]

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.

 

[steve the fish]

Ok paste here what is in both the master worksheet range A2:A10 and the dash worksheet range A2:A10.

 

[SplatP1]

here are both sheets.
Changing the dropdown in Dash! changes the value in A:A of report_master. This works great.
Now I need only the rows with a "1" in A:A shown in report_master! to be shown in sheet 3

 

[steve the fish]

Ah ok so try this:

=IFERROR(INDEX(report_master!C$2:C$10,SMALL(IF(report_master!$B$2:$B$10=Dash!$B$2,ROW(report_master!$A$2:$A$10)-1),ROWS($A$1:A1))),"")

Drag across and down. Change those ranges!! Row 10 is not large enough.

If the sheet is large id use code for this though.

 

[SplatP1]

hmmm, the sheet is currently 83233 rows and 25 columns
I'll try that formula now

 

[SplatP1]

it seems to work for the first 10 lines, but the block is at the $B$10 in the formula. I try and change it and it will not let me. it changes back to 10 as soon as I press enter.

 

[SplatP1]

Any idea what I am doing wrong?

 

[steve the fish]

The 83233 needs to replace the 10 in all of the report_master ranges. Then use CTRL-SHIFT-ENTER. This is a large range though so id definitely use code. You will find it tediously slow.

 

[Fluff]

What version of Xl are you using?
If you go to account settings, you can select your version & platform so that it appears in your mini-profile beside every post. Saves members asking.

 

[SplatP1]

added, thanks for the tip!

 

[SplatP1]

When I did that, the first cell went blank (attached).

Any code solutions or suggestions?