looping and filtering without Macro

purpleozzie

Board Regular
Joined
Jun 8, 2015
Messages
64
Hi guys,

In my spreadsheet there is a sheet called 'pre-packaged beer' which has a list of all beer we produce.

We produce some of our beers in keg, some in cask, and some in cans

I've used this formula on another sheet that pulls the list of beers in three times.. Once for Keg once for Cask and once for Cans...

Code:
=IF(ROW()<=COUNTA('Pre-Packaged Beer'!A:A)*3+4,INDEX('Pre-Packaged Beer'!A:A,MOD(ROW()-5,COUNTA('Pre-Packaged Beer'!A:A))+2),"")

On 'pre-packaged beer' sheet there are three columns O(Keg), P(Cask), Q(Cans) which have a Y in if we produce that product or N if we don't... What I want is for this formula to go down the list checking column O and if there's a 'N' skipping this line of data but not leaving a blank line if that all makes sense..

I also don't want to use Macros
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Sounds like a query might suit. Doesn't use macros or formulas. It is like a database query & refreshable like a pivot table. Suggest you refer Excel help or google.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,815
Office Version
  1. 365
Platform
  1. Windows
I've done this on a single sheet, but is this the sort of thing you are after?
Formula in S2 is copied across and down.

Excel Workbook
AOPQRSTU
1TypeKegCaskCansKegCaskCans
2Beer 1YNNBeer 1Beer 2Beer 2
3Beer 2NYYBeer 3Beer 4Beer 3
4Beer 3YNYBeer 7Beer 5Beer 8
5Beer 4NYNBeer 6Beer 9
6Beer 5NYNBeer 7
7Beer 6NYNBeer 8
8Beer 7YYN
9Beer 8NYY
10Beer 9NNY
11
Beers
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,063
Messages
5,851,869
Members
431,470
Latest member
mikaelaleksandrowicz

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
Top