How to extract data from range to a new worksheet if it fits 2 criteria

Hello DrewBoid,

Welcome to the forum

To shortly answer your question, yes, you can do so by using Array Formula, normal Formula with conditions, or VBA - VBA will be a much better approach if your data goes into the range of thousands of rows.

Here is a sample workbook with your provided data with some additional dummy data, to show you how an approach with INDIRECT will look like.
This approach emphasizes on performance, but it requires you to sort Master List by Supplier (Column B) and by Order in (Column C, Largest to Smallest)

I think VBA will be a much better solution if you have something like, 20 suppliers, and you want each of them on a new sheet lol. Or you can set it up so all you need is to type the Supplier name on the Sheet 2 and it will find it for you (Manually printing)

The problem with your data is that you have those header rows, which will be a mess when you sort to use Indirect xd

To keep your Master List as intact as possible, here is your file with Array Formula

Please Login or Register  to view this content.

Because it is an Array formula, remember to confirm it with Ctrl-Shift-Enter, not just Enter. A simple way to do this is to paste the formula in the cell, then click on the formula bar, hold Ctrl-Shift and hit Enter. If the formula is wrapped inside a { } afterward, then you did it right.

The Formula is for cell A2, applied from A2 to I400, with 2 simple helper cells which are:
J2: Simply the name of the supplier you wanna check and
K2: =COUNTIF('Stock Take Wet Stock'!$B$6:$B$394,$J$2)
Counting how many rows that has that Supplier's name (For reference, checking and whatsnot)

Unfortunately, you will still need to filter out rows with 0 value on Order in Column. I'll see what I can do to counter this, chances are the formula will get pretty big lol

Simply change the name in J2 (Supplier1, Supplier2, etc) and watch as the file changes itself. I've applied formula up to row 400, if your Master List gets bigger, you might wanna drag the formula from A400:I400 down to match your Maste List.

Edit: Removed old sample, added a better one in the post below this one.

As soon as I editted my post, I found the solution to the 0 (Curse you AND function)

Here is the improved Array Formula:

Please Login or Register  to view this content.

I also added in 1 more helper cell to show you how many results there are (So you can easily calculate how many orders from that Supplier is 0)

And here is the sample file with it. Please still read the post above this one still.

You can remove the Iferror and you will see that at the end of the list, instead of "blank" value, there will be errors

The format of Iferror is

IFERROR(Something, Value if Something is an error)

In this case, that Something is our formula, and the Value if Something is an error is "" (blank value if error). It will return the entire value of Something if it is not an error.

For example, there are only 80 rows with Supplier6 name on it, and the row number 82 was forced to return the row 82nd that has the name Supplier6. It doesns't exist, thus returning an Error. The Iferror in front of it is to prevent that case, which will return a "" value if the formula in it returns an error.

In short, it's there to prevent cases where nothing was found basing on the conditions of the formula (Matching Supplier's name, Order in must not be 0, etc)

Of course, if the formula returns a non-error value, then that's great, Iferror will return just that.

If you want me to break down any part of the formula, let me know.

Change the following thing

{=IFERROR(INDEX('Stock Take Wet Stock'!A$1:A$394,SMALL(IF(('Stock Take Wet Stock'!$C$1:$C$394<>0)*('Stock Take Wet Stock'!$B$1:$B$394=$J$2),ROW('Stock Take Wet Stock'!$B$1:$B$394)),ROW()-1)),"")}

ROW() will return the number of the row the formula is in. If you want to move the first row with formula down to row 5 for example, make sure that ROW()-1 on that row have to return 1, since it will take the first result, so change the formula to

{=IFERROR(INDEX('Stock Take Wet Stock'!A$1:A$394,SMALL(IF(('Stock Take Wet Stock'!$C$1:$C$394<>0)*('Stock Take Wet Stock'!$B$1:$B$394=$J$2),ROW('Stock Take Wet Stock'!$B$1:$B$394)),ROW()-4)),"")}

(This is for row 5)

So, row 3? ROW()-2. What about row 7? ROW()-6

It should not be, because that function counts the number of times the value in J2, aka Supplier's name, appears in a row, unless your real data's range is not from B6 to B394 but bigger.

Scratch that, for some reason, when I do filter on Supplier, I found 2 Suppler2 which are
"Supplier2"
"Supplier2 " (Found in B113)

You see what's going on? For some reason, somewhere in there, there is a trailing character at the end. For some reason, TRIM does not work on it on my end, you might want to do a forced counting / manually fixing those values.